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: 

EXPLAIN SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = ’2000-01-01’ 

The execution plan depends on whether the extended index is used. 

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 . 

