Use of Index Extensions The optimizer can use extended secondary indexes for  ref ,  range , and  index_merge  index access, for Loose Index Scan access, for join and sorting optimization, and for  MIN() / MAX()  optimization. The following example shows how execution plans are affected by whether the optimizer uses extended secondary indexes. Suppose that  t1  is populated with these rows: INSERT INTO t1 VALUES (1, 1, ’1998-01-01’), (1, 2, ’1999-01-01’), (1, 3, ’2000-01-01’), (1, 4, ’2001-01-01’), (1, 5, ’2002-01-01’), (2, 1, ’1998-01-01’), (2, 2, ’1999-01-01’), (2, 3, ’2000-01-01’), (2, 4, ’2001-01-01’), (2, 5, ’2002-01-01’), (3, 1, ’1998-01-01’), (3, 2, ’1999-01-01’), (3, 3, ’2000-01-01’), (3, 4, ’2001-01-01’), (3, 5, ’2002-01-01’), (4, 1, ’1998-01-01’), (4, 2, ’1999-01-01’), (4, 3, ’2000-01-01’), (4, 4, ’2001-01-01’), (4, 5, ’2002-01-01’), (5, 1, ’1998-01-01’), (5, 2, ’1999-01-01’), (5, 3, ’2000-01-01’), (5, 4, ’2001-01-01’), (5, 5, ’2002-01-01’); Now consider this query: The execution plan depends on whether the extended index is used. EXPLAIN SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = ’2000-01-01’ When the optimizer does not consider index extensions, it treats the index  k_d  as only  (d) .  EXPLAIN  for the query produces this result: mysql>  EXPLAIN SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = ’2000-01-01’\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 type: ref possible_keys: PRIMARY,k_d key: k_d key_len: 4 ref: const rows: 5 Extra: Using where; Using index When the optimizer takes index extensions into account, it treats  k_d  as  (d, i1, i2) . In this case, it can use the leftmost index prefix  (d, i1)  to produce a better execution plan: mysql>  EXPLAIN SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = ’2000-01-01’\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 type: ref possible_keys: PRIMARY,k_d key: k_d key_len: 8 ref: const,const rows: 1 Extra: Using index In both cases,  key  indicates that the optimizer uses secondary index  k_d  but the  EXPLAIN  output shows these improvements from using the extended index: • key_len  goes from 4 bytes to 8 bytes, indicating that key lookups use columns  d  and  i1 , not just  d . 1724