•They are updated for table modifications ( INSERT , UPDATE , DELETE , and so forth). Updates occur as though the index was Cartesian, even though the column might contain a mix of Cartesian and geographical values. 

•They exist only for backward compatibility (for example, the ability to perform a dump in MySQL 5.7 and restore in MySQL 8.0). Because SPATIAL  indexes on columns that are not SRID-restricted are of no use to the optimizer, each such column should be modified: 

•Verify that all values within the column have the same SRID. To determine the SRIDs contained in a geometry column col_name , use the following query: 

SELECT DISTINCT ST_SRID( col_name ) FROM tbl_name ; 

If the query returns more than one row, the column contains a mix of SRIDs. In that case, modify its contents so all values have the same SRID. 

## 8.3.4 Foreign Key Optimization 

•Redefine the column to have an explicit SRID  attribute. 

•Recreate the SPATIAL  index. 

If a table has many columns, and you query many different combinations of columns, it might be efficient to split the less-frequently used data into separate tables with a few columns each, and relate them back to the main table by duplicating the numeric ID column from the main table. That way, each small table can have a primary key for fast lookups of its data, and you can query just the set of columns that you need using a join operation. Depending on how the data is distributed, the queries might perform less I/O and take up less cache memory because the relevant columns are packed together on disk. (To maximize performance, queries try to read as few data blocks as possible from disk; tables with only a few columns can fit more rows in each data block.) 

## 8.3.5 Column Indexes 

The most common type of index involves a single column, storing copies of the values from that column in a data structure, allowing fast lookups for the rows with the corresponding column values. The B-tree data structure lets the index quickly find a specific value, a set of values, or a range of values, corresponding to operators such as = , > , ≤ , BETWEEN , IN , and so on, in a WHERE  clause. 

The maximum number of indexes per table and the maximum index length is defined per storage engine. See Chapter 15, The InnoDB Storage Engine , and Chapter 16, Alternative Storage Engines . All storage engines support at least 16 indexes per table and a total index length of at least 256 bytes. Most storage engines have higher limits. 

For additional information about column indexes, see Section 13.1.15, “CREATE INDEX Statement” . 

## Index Prefixes 

•Index Prefixes 

•FULLTEXT Indexes 

•Spatial Indexes 

•Indexes in the MEMORY Storage Engine 

With col_name ( N )  syntax in an index specification for a string column, you can create an index that uses only the first N  characters of the column. Indexing only a prefix of column values in this way can make the 

