With MySQL 5.7.4 LAB release, InnoDB now supports “Spatial Index” on geometry data.

Before this new feature, InnoDB stores geometry data as BLOB data, and only prefix index can be created on the spatial data. It is very inefficient when comes to spatial search, especially when it comes to complex geometry data. In most cases, table scan are the only way to get the result. This all changed with InnoDB spatial index, which is implemented as R-tree, any spatial search becomes far more efficient.

InnoDB spatial index can be used with all existing syntax that has been developed for MyISAM spatial index. In addition, InnoDB spatial index supports full transaction properties, as well as isolation levels. It employs predicate lock to prevent phantom scenario.

In InnoDB spatial index, only the object’s Minimum Bounding Box is included in the index, making the index entry size small and packed, even with large, complex geometry data. The index itself serves as first screening process, which picks out “possible candidates” with the search criteria (in terms of bounding boxes), and then the data stored in primary index is fetched to server for more detail process.

Currently, InnoDB spatial index supports only two dimension data, but we do have plan to extend to multi-dimension. In addition, we are doing more performance tuning to make it more efficient.

Stay tuned for more discussion on details about InnoDB spatial indexes in the coming weeks.