that occur before key_col  in the index. In this case, MySQL does a single key lookup for each MIN() or MAX()  expression and replaces it with a constant. If all expressions are replaced with constants, the query returns at once. For example: 

SELECT MIN( key_part2 ),MAX( key_part2 ) FROM tbl_name  WHERE key_part1 =10; 

•To sort or group a table if the sorting or grouping is done on a leftmost prefix of a usable index (for example, ORDER BY key_part1 , key_part2 ). If all key parts are followed by DESC , the key is read in reverse order. (Or, if the index is a descending index, the key is read in forward order.) See Section 8.2.1.16, “ORDER BY Optimization” , Section 8.2.1.17, “GROUP BY Optimization” , and Section 8.3.13, “Descending Indexes” . 

•In some cases, a query can be optimized to retrieve values without consulting the data rows. (An index that provides all the necessary results for a query is called a covering index.) If a query uses from a table only columns that are included in some index, the selected values can be retrieved from the index tree for greater speed: 

SELECT key_part3  FROM tbl_name WHERE key_part1 =1 

Indexes are less important for queries on small tables, or big tables where report queries process most or all of the rows. When a query needs to access most of the rows, reading sequentially is faster than working through an index. Sequential reads minimize disk seeks, even if not all the rows are needed for the query. See Section 8.2.1.23, “Avoiding Full Table Scans”  for details. 

## 8.3.2 Primary Key Optimization 

The primary key for a table represents the column or set of columns that you use in your most vital queries. It has an associated index, for fast query performance. Query performance benefits from the NOT NULL optimization, because it cannot include any NULL  values. With the InnoDB  storage engine, the table data is physically organized to do ultra-fast lookups and sorts based on the primary key column or columns. 

If your table is big and important, but does not have an obvious column or set of columns to use as a primary key, you might create a separate column with auto-increment values to use as the primary key. These unique IDs can serve as pointers to corresponding rows in other tables when you join tables using foreign keys. 

## 8.3.3 SPATIAL Index Optimization 

MySQL permits creation of SPATIAL  indexes on NOT NULL  geometry-valued columns (see Section 11.4.10, “Creating Spatial Indexes” ). The optimizer checks the SRID  attribute for indexed columns to determine which spatial reference system (SRS) to use for comparisons, and uses calculations appropriate to the SRS. (Prior to MySQL 8.0, the optimizer performs comparisons of SPATIAL  index values using Cartesian calculations; the results of such operations are undefined if the column contains values with non-Cartesian SRIDs.) 

For comparisons to work properly, each column in a SPATIAL  index must be SRID-restricted. That is, the column definition must include an explicit SRID  attribute, and all column values must have the same SRID. 

The optimizer considers SPATIAL  indexes only for SRID-restricted columns: 

•Indexes on columns restricted to a Cartesian SRID enable Cartesian bounding box computations. 

•Indexes on columns restricted to a geographic SRID enable geographic bounding box computations. 

The optimizer ignores SPATIAL  indexes on columns that have no SRID  attribute (and thus are not SRID- restricted). MySQL still maintains such indexes, as follows: 

