-------------- /* SQL Script * * The following commands were executed on the `employees` database, availabe from https://launchpad.net/test-db/ */ USE employees -------------- Query OK, 0 rows affected (0.00 sec) -------------- SELECT VERSION() AS 'MySQL Version' -------------- +------------------+ | MySQL Version | +------------------+ | 5.1.46-community | +------------------+ 1 row in set (0.00 sec) -------------- /* Creating an index for an index-covered query */ CREATE INDEX dept_no_from_date_idx ON dept_emp (dept_no, from_date) -------------- Query OK, 331603 rows affected (29.67 sec) Records: 331603 Duplicates: 0 Warnings: 0 -------------- /* Show `dept_emp` table structure, indexes and generic data */ SHOW TABLE STATUS LIKE "dept_emp" -------------- +----------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +----------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+ | dept_emp | InnoDB | 10 | Compact | 331883 | 36 | 12075008 | 0 | 21544960 | 29360128 | NULL | 2010-05-04 13:07:49 | NULL | NULL | utf8_general_ci | NULL | | | +----------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+ 1 row in set (0.22 sec) -------------- DESCRIBE dept_emp -------------- +-----------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+---------+------+-----+---------+-------+ | emp_no | int(11) | NO | PRI | NULL | | | dept_no | char(4) | NO | PRI | NULL | | | from_date | date | NO | | NULL | | | to_date | date | NO | | NULL | | +-----------+---------+------+-----+---------+-------+ 4 rows in set (0.00 sec) -------------- SHOW KEYS IN dept_emp -------------- +----------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +----------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | dept_emp | 0 | PRIMARY | 1 | emp_no | A | 331883 | NULL | NULL | | BTREE | | | dept_emp | 0 | PRIMARY | 2 | dept_no | A | 331883 | NULL | NULL | | BTREE | | | dept_emp | 1 | emp_no | 1 | emp_no | A | 331883 | NULL | NULL | | BTREE | | | dept_emp | 1 | dept_no | 1 | dept_no | A | 7 | NULL | NULL | | BTREE | | | dept_emp | 1 | dept_no_from_date_idx | 1 | dept_no | A | 142 | NULL | NULL | | BTREE | | | dept_emp | 1 | dept_no_from_date_idx | 2 | from_date | A | 165941 | NULL | NULL | | BTREE | | +----------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 6 rows in set (0.16 sec) -------------- /* The EXPLAIN shows that the subquery doesn't use a covering-index */ EXPLAIN SELECT SQL_NO_CACHE * FROM dept_emp INNER JOIN ( /* The subquery should use a covering index, but apparently isn't */ SELECT SQL_NO_CACHE emp_no, dept_no FROM dept_emp WHERE dept_no="d001" ORDER BY from_date DESC LIMIT 20000,50 ) AS `der` USING (`emp_no`, `dept_no`) -------------- +----+-------------+------------+--------+----------------------------------------------+-----------------------+---------+------------------------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+--------+----------------------------------------------+-----------------------+---------+------------------------+-------+-------------+ | 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 50 | | | 1 | PRIMARY | dept_emp | eq_ref | PRIMARY,emp_no,dept_no,dept_no_from_date_idx | PRIMARY | 16 | der.emp_no,der.dept_no | 1 | | | 2 | DERIVED | dept_emp | ref | dept_no,dept_no_from_date_idx | dept_no_from_date_idx | 12 | | 21402 | Using where | +----+-------------+------------+--------+----------------------------------------------+-----------------------+---------+------------------------+-------+-------------+ 3 rows in set (0.20 sec) -------------- /* The EXPLAIN shows that the subquery DOES use a covering-index, thanks to the FORCE INDEX clause */ EXPLAIN SELECT SQL_NO_CACHE * FROM dept_emp INNER JOIN ( /* The subquery uses a covering index */ SELECT SQL_NO_CACHE emp_no, dept_no FROM dept_emp FORCE INDEX(dept_no_from_date_idx) WHERE dept_no="d001" ORDER BY from_date DESC LIMIT 20000,50 ) AS `der` USING (`emp_no`, `dept_no`) -------------- +----+-------------+------------+--------+----------------------------------------------+-----------------------+---------+------------------------+-------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+--------+----------------------------------------------+-----------------------+---------+------------------------+-------+--------------------------+ | 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 50 | | | 1 | PRIMARY | dept_emp | eq_ref | PRIMARY,emp_no,dept_no,dept_no_from_date_idx | PRIMARY | 16 | der.emp_no,der.dept_no | 1 | | | 2 | DERIVED | dept_emp | ref | dept_no_from_date_idx | dept_no_from_date_idx | 12 | | 37468 | Using where; Using index | +----+-------------+------------+--------+----------------------------------------------+-----------------------+---------+------------------------+-------+--------------------------+ 3 rows in set (0.05 sec) -------------- /* * With execution speed measurement, it is easy to see that both of the above queries use a covering index, * despite that "Using index" isn't mentioned in the first query's EXPLAIN output. */ /* The following two queries use (and should) a covering index */ SELECT SQL_NO_CACHE * FROM dept_emp INNER JOIN ( SELECT SQL_NO_CACHE emp_no, dept_no FROM dept_emp WHERE dept_no="d001" ORDER BY from_date DESC LIMIT 20000,50 ) AS `der` USING (`emp_no`, `dept_no`) -------------- +--------+---------+------------+------------+ | emp_no | dept_no | from_date | to_date | +--------+---------+------------+------------+ | 38552 | d001 | 1985-04-16 | 2000-10-20 | | 288642 | d001 | 1985-04-15 | 9999-01-01 | | 453203 | d001 | 1985-04-14 | 1987-12-26 | | 407968 | d001 | 1985-04-14 | 9999-01-01 | | 277681 | d001 | 1985-04-13 | 1996-02-02 | | 204607 | d001 | 1985-04-12 | 9999-01-01 | | 91029 | d001 | 1985-04-12 | 9999-01-01 | | 87359 | d001 | 1985-04-12 | 1993-10-23 | | 17668 | d001 | 1985-04-12 | 9999-01-01 | | 289556 | d001 | 1985-04-11 | 9999-01-01 | | 106441 | d001 | 1985-04-11 | 9999-01-01 | | 36731 | d001 | 1985-04-11 | 9999-01-01 | | 266984 | d001 | 1985-04-10 | 9999-01-01 | | 102344 | d001 | 1985-04-10 | 9999-01-01 | | 81167 | d001 | 1985-04-10 | 9999-01-01 | | 27426 | d001 | 1985-04-10 | 9999-01-01 | | 477337 | d001 | 1985-04-09 | 9999-01-01 | | 424654 | d001 | 1985-04-09 | 9999-01-01 | | 269911 | d001 | 1985-04-09 | 9999-01-01 | | 208842 | d001 | 1985-04-09 | 9999-01-01 | | 91217 | d001 | 1985-04-09 | 9999-01-01 | | 428310 | d001 | 1985-04-08 | 9999-01-01 | | 236591 | d001 | 1985-04-08 | 9999-01-01 | | 67766 | d001 | 1985-04-08 | 9999-01-01 | | 486326 | d001 | 1985-04-07 | 9999-01-01 | | 282776 | d001 | 1985-04-07 | 9999-01-01 | | 266821 | d001 | 1985-04-06 | 9999-01-01 | | 467473 | d001 | 1985-04-05 | 9999-01-01 | | 239996 | d001 | 1985-04-05 | 1991-08-19 | | 210506 | d001 | 1985-04-05 | 1991-11-05 | | 432296 | d001 | 1985-04-04 | 9999-01-01 | | 235882 | d001 | 1985-04-04 | 9999-01-01 | | 84386 | d001 | 1985-04-04 | 9999-01-01 | | 83344 | d001 | 1985-04-04 | 9999-01-01 | | 442659 | d001 | 1985-04-03 | 1993-12-04 | | 283739 | d001 | 1985-04-03 | 9999-01-01 | | 270211 | d001 | 1985-04-03 | 9999-01-01 | | 75711 | d001 | 1985-04-03 | 9999-01-01 | | 465801 | d001 | 1985-04-02 | 9999-01-01 | | 408128 | d001 | 1985-04-02 | 9999-01-01 | | 41724 | d001 | 1985-04-02 | 9999-01-01 | | 408743 | d001 | 1985-04-01 | 9999-01-01 | | 82572 | d001 | 1985-04-01 | 9999-01-01 | | 243523 | d001 | 1985-03-30 | 9999-01-01 | | 234860 | d001 | 1985-03-30 | 9999-01-01 | | 223370 | d001 | 1985-03-30 | 2000-06-08 | | 249200 | d001 | 1985-03-29 | 9999-01-01 | | 261302 | d001 | 1985-03-28 | 9999-01-01 | | 253042 | d001 | 1985-03-28 | 9999-01-01 | | 98045 | d001 | 1985-03-28 | 9999-01-01 | +--------+---------+------------+------------+ 50 rows in set (0.25 sec) -------------- SELECT SQL_NO_CACHE * FROM dept_emp INNER JOIN ( SELECT SQL_NO_CACHE emp_no, dept_no FROM dept_emp FORCE INDEX(dept_no_from_date_idx) WHERE dept_no="d001" ORDER BY from_date DESC LIMIT 20000,50 ) AS `der` USING (`emp_no`, `dept_no`) -------------- +--------+---------+------------+------------+ | emp_no | dept_no | from_date | to_date | +--------+---------+------------+------------+ | 38552 | d001 | 1985-04-16 | 2000-10-20 | | 288642 | d001 | 1985-04-15 | 9999-01-01 | | 453203 | d001 | 1985-04-14 | 1987-12-26 | | 407968 | d001 | 1985-04-14 | 9999-01-01 | | 277681 | d001 | 1985-04-13 | 1996-02-02 | | 204607 | d001 | 1985-04-12 | 9999-01-01 | | 91029 | d001 | 1985-04-12 | 9999-01-01 | | 87359 | d001 | 1985-04-12 | 1993-10-23 | | 17668 | d001 | 1985-04-12 | 9999-01-01 | | 289556 | d001 | 1985-04-11 | 9999-01-01 | | 106441 | d001 | 1985-04-11 | 9999-01-01 | | 36731 | d001 | 1985-04-11 | 9999-01-01 | | 266984 | d001 | 1985-04-10 | 9999-01-01 | | 102344 | d001 | 1985-04-10 | 9999-01-01 | | 81167 | d001 | 1985-04-10 | 9999-01-01 | | 27426 | d001 | 1985-04-10 | 9999-01-01 | | 477337 | d001 | 1985-04-09 | 9999-01-01 | | 424654 | d001 | 1985-04-09 | 9999-01-01 | | 269911 | d001 | 1985-04-09 | 9999-01-01 | | 208842 | d001 | 1985-04-09 | 9999-01-01 | | 91217 | d001 | 1985-04-09 | 9999-01-01 | | 428310 | d001 | 1985-04-08 | 9999-01-01 | | 236591 | d001 | 1985-04-08 | 9999-01-01 | | 67766 | d001 | 1985-04-08 | 9999-01-01 | | 486326 | d001 | 1985-04-07 | 9999-01-01 | | 282776 | d001 | 1985-04-07 | 9999-01-01 | | 266821 | d001 | 1985-04-06 | 9999-01-01 | | 467473 | d001 | 1985-04-05 | 9999-01-01 | | 239996 | d001 | 1985-04-05 | 1991-08-19 | | 210506 | d001 | 1985-04-05 | 1991-11-05 | | 432296 | d001 | 1985-04-04 | 9999-01-01 | | 235882 | d001 | 1985-04-04 | 9999-01-01 | | 84386 | d001 | 1985-04-04 | 9999-01-01 | | 83344 | d001 | 1985-04-04 | 9999-01-01 | | 442659 | d001 | 1985-04-03 | 1993-12-04 | | 283739 | d001 | 1985-04-03 | 9999-01-01 | | 270211 | d001 | 1985-04-03 | 9999-01-01 | | 75711 | d001 | 1985-04-03 | 9999-01-01 | | 465801 | d001 | 1985-04-02 | 9999-01-01 | | 408128 | d001 | 1985-04-02 | 9999-01-01 | | 41724 | d001 | 1985-04-02 | 9999-01-01 | | 408743 | d001 | 1985-04-01 | 9999-01-01 | | 82572 | d001 | 1985-04-01 | 9999-01-01 | | 243523 | d001 | 1985-03-30 | 9999-01-01 | | 234860 | d001 | 1985-03-30 | 9999-01-01 | | 223370 | d001 | 1985-03-30 | 2000-06-08 | | 249200 | d001 | 1985-03-29 | 9999-01-01 | | 261302 | d001 | 1985-03-28 | 9999-01-01 | | 253042 | d001 | 1985-03-28 | 9999-01-01 | | 98045 | d001 | 1985-03-28 | 9999-01-01 | +--------+---------+------------+------------+ 50 rows in set (0.05 sec) -------------- /* The following query doesn't (and shouldn't) use a covering index */ SELECT SQL_NO_CACHE * FROM dept_emp INNER JOIN ( SELECT SQL_NO_CACHE `to_date`, emp_no, dept_no FROM dept_emp WHERE dept_no="d001" ORDER BY from_date DESC LIMIT 20000,50 ) AS `der` USING (`emp_no`, `dept_no`) -------------- +--------+---------+------------+------------+------------+ | emp_no | dept_no | from_date | to_date | to_date | +--------+---------+------------+------------+------------+ | 38552 | d001 | 1985-04-16 | 2000-10-20 | 2000-10-20 | | 288642 | d001 | 1985-04-15 | 9999-01-01 | 9999-01-01 | | 453203 | d001 | 1985-04-14 | 1987-12-26 | 1987-12-26 | | 407968 | d001 | 1985-04-14 | 9999-01-01 | 9999-01-01 | | 277681 | d001 | 1985-04-13 | 1996-02-02 | 1996-02-02 | | 204607 | d001 | 1985-04-12 | 9999-01-01 | 9999-01-01 | | 91029 | d001 | 1985-04-12 | 9999-01-01 | 9999-01-01 | | 87359 | d001 | 1985-04-12 | 1993-10-23 | 1993-10-23 | | 17668 | d001 | 1985-04-12 | 9999-01-01 | 9999-01-01 | | 289556 | d001 | 1985-04-11 | 9999-01-01 | 9999-01-01 | | 106441 | d001 | 1985-04-11 | 9999-01-01 | 9999-01-01 | | 36731 | d001 | 1985-04-11 | 9999-01-01 | 9999-01-01 | | 266984 | d001 | 1985-04-10 | 9999-01-01 | 9999-01-01 | | 102344 | d001 | 1985-04-10 | 9999-01-01 | 9999-01-01 | | 81167 | d001 | 1985-04-10 | 9999-01-01 | 9999-01-01 | | 27426 | d001 | 1985-04-10 | 9999-01-01 | 9999-01-01 | | 477337 | d001 | 1985-04-09 | 9999-01-01 | 9999-01-01 | | 424654 | d001 | 1985-04-09 | 9999-01-01 | 9999-01-01 | | 269911 | d001 | 1985-04-09 | 9999-01-01 | 9999-01-01 | | 208842 | d001 | 1985-04-09 | 9999-01-01 | 9999-01-01 | | 91217 | d001 | 1985-04-09 | 9999-01-01 | 9999-01-01 | | 428310 | d001 | 1985-04-08 | 9999-01-01 | 9999-01-01 | | 236591 | d001 | 1985-04-08 | 9999-01-01 | 9999-01-01 | | 67766 | d001 | 1985-04-08 | 9999-01-01 | 9999-01-01 | | 486326 | d001 | 1985-04-07 | 9999-01-01 | 9999-01-01 | | 282776 | d001 | 1985-04-07 | 9999-01-01 | 9999-01-01 | | 266821 | d001 | 1985-04-06 | 9999-01-01 | 9999-01-01 | | 467473 | d001 | 1985-04-05 | 9999-01-01 | 9999-01-01 | | 239996 | d001 | 1985-04-05 | 1991-08-19 | 1991-08-19 | | 210506 | d001 | 1985-04-05 | 1991-11-05 | 1991-11-05 | | 432296 | d001 | 1985-04-04 | 9999-01-01 | 9999-01-01 | | 235882 | d001 | 1985-04-04 | 9999-01-01 | 9999-01-01 | | 84386 | d001 | 1985-04-04 | 9999-01-01 | 9999-01-01 | | 83344 | d001 | 1985-04-04 | 9999-01-01 | 9999-01-01 | | 442659 | d001 | 1985-04-03 | 1993-12-04 | 1993-12-04 | | 283739 | d001 | 1985-04-03 | 9999-01-01 | 9999-01-01 | | 270211 | d001 | 1985-04-03 | 9999-01-01 | 9999-01-01 | | 75711 | d001 | 1985-04-03 | 9999-01-01 | 9999-01-01 | | 465801 | d001 | 1985-04-02 | 9999-01-01 | 9999-01-01 | | 408128 | d001 | 1985-04-02 | 9999-01-01 | 9999-01-01 | | 41724 | d001 | 1985-04-02 | 9999-01-01 | 9999-01-01 | | 408743 | d001 | 1985-04-01 | 9999-01-01 | 9999-01-01 | | 82572 | d001 | 1985-04-01 | 9999-01-01 | 9999-01-01 | | 243523 | d001 | 1985-03-30 | 9999-01-01 | 9999-01-01 | | 234860 | d001 | 1985-03-30 | 9999-01-01 | 9999-01-01 | | 223370 | d001 | 1985-03-30 | 2000-06-08 | 2000-06-08 | | 249200 | d001 | 1985-03-29 | 9999-01-01 | 9999-01-01 | | 261302 | d001 | 1985-03-28 | 9999-01-01 | 9999-01-01 | | 253042 | d001 | 1985-03-28 | 9999-01-01 | 9999-01-01 | | 98045 | d001 | 1985-03-28 | 9999-01-01 | 9999-01-01 | +--------+---------+------------+------------+------------+ 50 rows in set (4.22 sec) Bye