Optimizer Use of Generated Column Indexes •The  ref  value changes from  const  to  const,const  because the key lookup uses two key parts, not one. •The  rows  count decreases from 5 to 1, indicating that  InnoDB  should need to examine fewer rows to produce the result. •The  Extra  value changes from  Using where; Using index  to  Using index . This means that rows can be read using only the index, without consulting columns in the data row. Differences in optimizer behavior for use of extended indexes can also be seen with  SHOW STATUS : FLUSH TABLE t1; FLUSH STATUS; SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = ’2000-01-01’; SHOW STATUS LIKE ’handler_read%’ The preceding statements include  FLUSH TABLES  and  FLUSH STATUS  to flush the table cache and clear the status counters. Without index extensions,  SHOW STATUS  produces this result: +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | Handler_read_first | 0 | | Handler_read_key | 1 | | Handler_read_last | 0 | | Handler_read_next | 5 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 0 | +-----------------------+-------+ +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | Handler_read_first | 0 | | Handler_read_key | 1 | | Handler_read_last | 0 | | Handler_read_next | 1 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 0 | +-----------------------+-------+ With index extensions,  SHOW STATUS  produces this result. The  Handler_read_next  value decreases from 5 to 1, indicating more efficient use of the index: The  use_index_extensions  flag of the  optimizer_switch  system variable permits control over whether the optimizer takes the primary key columns into account when determining how to use an InnoDB  table’s secondary indexes. By default,  use_index_extensions  is enabled. To check whether disabling use of index extensions can improve performance, use this statement: Use of index extensions by the optimizer is subject to the usual limits on the number of key parts in an index (16) and the maximum key length (3072 bytes). MySQL supports indexes on generated columns. For example: SET optimizer_switch = ’use_index_extensions=off’; CREATE TABLE t1 (f1 INT, gc INT AS (f1 + 1) STORED, INDEX (gc)); 8.3.11 Optimizer Use of Generated Column Indexes 1725