Foreign Key Optimization •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. •Redefine the column to have an explicit  SRID  attribute. •Recreate the  SPATIAL  index. 8.3.4 Foreign Key Optimization 8.3.5 Column Indexes Index Prefixes 1717 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 For additional information about column indexes, see Section 13.1.15, “CREATE INDEX Statement” . 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. 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. 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.) •Index Prefixes •FULLTEXT Indexes •Spatial Indexes •Indexes in the MEMORY Storage Engine