patternsqlModerate
Must an index cover all selected columns for it to be used for ORDER BY?
Viewed 0 times
ordermustallcolumnsusedselectedforindexcover
Problem
Over at SO, someone recently asked Why isn't ORDER BY using the index?
The situation involved a simple InnoDB table in MySQL comprising three columns and 10k rows. One of the columns, an integer, was indexed—and the OP sought to retrieve his entire table sorted on that column:
He attached
Despite the hint
This appears to fly in the face of the manual chapter on
The following queries use the index to resolve the
To my reading, that is precisely the case in this situation (yet the index was not being used without an explicit hint).
My questions are:
-
Is it indeed necessary for all selected columns to be indexed in order for MySQL to choose to use the index?
-
If so, where is this documented (if at all)?
-
If not, what was going on here?
The situation involved a simple InnoDB table in MySQL comprising three columns and 10k rows. One of the columns, an integer, was indexed—and the OP sought to retrieve his entire table sorted on that column:
SELECT * FROM person ORDER BY ageHe attached
EXPLAIN output showing that this query was resolved with a filesort (rather than the index) and asked why that would be.Despite the hint
FORCE INDEX FOR ORDER BY (age) causing the index to be used, someone answered (with supporting comments/upvotes from others) that an index is only used for sorting when the selected columns are all read from the index (i.e. as would normally be indicated by Using index in the Extra column of EXPLAIN output). An explanation was later given that traversing the index and then fetching columns from the table results in random I/O, which MySQL views as more expensive than a filesort.This appears to fly in the face of the manual chapter on
ORDER BY Optimization, which not only conveys the strong impression that satisfying ORDER BY from an index is preferable to performing additional sorting (indeed, filesort is a combination of quicksort and mergesort and therefore must have a lower bound of Ω(nlog n); whilst walking through the index in order and seeking into the table ought to be O(n)—so this makes perfect sense), but it also neglects to mention this alleged "optimisation" whilst also stating:The following queries use the index to resolve the
ORDER BY part:SELECT * FROM t1
ORDER BY key_part1,key_part2,... ;To my reading, that is precisely the case in this situation (yet the index was not being used without an explicit hint).
My questions are:
-
Is it indeed necessary for all selected columns to be indexed in order for MySQL to choose to use the index?
-
If so, where is this documented (if at all)?
-
If not, what was going on here?
Solution
Is it indeed necessary for all selected columns to be indexed in order for MySQL to choose to use the index?
This is a loaded question because there are factors that determine whether an index is worth using.
FACTOR #1
For any given index, what is the key population? In other words, what is the cardinality (distinct count) of all tuples recorded in the index?
FACTOR #2
What storage engine are you using? Are all needed columns accessible from an index?
WHAT'S NEXT ???
Let's take a simple example: a table that holds two values (Male and Female)
Let create such a table with a test for index usage
TEST InnoDB
TEST MyISAM
```
mysql> USE test
Database changed
mysql> DROP TABLE IF EXISTS mf;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE mf
->
This is a loaded question because there are factors that determine whether an index is worth using.
FACTOR #1
For any given index, what is the key population? In other words, what is the cardinality (distinct count) of all tuples recorded in the index?
FACTOR #2
What storage engine are you using? Are all needed columns accessible from an index?
WHAT'S NEXT ???
Let's take a simple example: a table that holds two values (Male and Female)
Let create such a table with a test for index usage
USE test
DROP TABLE IF EXISTS mf;
CREATE TABLE mf
(
id int not null auto_increment,
gender char(1),
primary key (id),
key (gender)
) ENGINE=InnODB;
INSERT INTO mf (gender) VALUES
('M'),('M'),('M'),('M'),('M'),('M'),('M'),('M'),
('M'),('M'),('M'),('M'),('F'),('F'),('M'),('M'),
('M'),('M'),('M'),('M'),('M'),('M'),('M'),('M'),
('M'),('M'),('M'),('M'),('M'),('M'),('M'),('M'),
('F'),('M'),('M'),('M'),('M'),('M'),('M'),('M');
ANALYZE TABLE mf;
EXPLAIN SELECT gender FROM mf WHERE gender='F';
EXPLAIN SELECT gender FROM mf WHERE gender='M';
EXPLAIN SELECT id FROM mf WHERE gender='F';
EXPLAIN SELECT id FROM mf WHERE gender='M';TEST InnoDB
mysql> USE test
Database changed
mysql> DROP TABLE IF EXISTS mf;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE mf
-> (
-> id int not null auto_increment,
-> gender char(1),
-> primary key (id),
-> key (gender)
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.07 sec)
mysql> INSERT INTO mf (gender) VALUES
-> ('M'),('M'),('M'),('M'),('M'),('M'),('M'),('M'),
-> ('M'),('M'),('M'),('M'),('F'),('F'),('M'),('M'),
-> ('M'),('M'),('M'),('M'),('M'),('M'),('M'),('M'),
-> ('M'),('M'),('M'),('M'),('M'),('M'),('M'),('M'),
-> ('F'),('M'),('M'),('M'),('M'),('M'),('M'),('M');
Query OK, 40 rows affected (0.06 sec)
Records: 40 Duplicates: 0 Warnings: 0
mysql> ANALYZE TABLE mf;
+---------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------+---------+----------+----------+
| test.mf | analyze | status | OK |
+---------+---------+----------+----------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT gender FROM mf WHERE gender='F';
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
| 1 | SIMPLE | mf | ref | gender | gender | 2 | const | 3 | Using where; Using index |
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT gender FROM mf WHERE gender='M';
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
| 1 | SIMPLE | mf | ref | gender | gender | 2 | const | 37 | Using where; Using index |
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT id FROM mf WHERE gender='F';
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
| 1 | SIMPLE | mf | ref | gender | gender | 2 | const | 3 | Using where; Using index |
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT id FROM mf WHERE gender='M';
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
| 1 | SIMPLE | mf | ref | gender | gender | 2 | const | 37 | Using where; Using index |
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)
mysql>TEST MyISAM
```
mysql> USE test
Database changed
mysql> DROP TABLE IF EXISTS mf;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE mf
->
Code Snippets
USE test
DROP TABLE IF EXISTS mf;
CREATE TABLE mf
(
id int not null auto_increment,
gender char(1),
primary key (id),
key (gender)
) ENGINE=InnODB;
INSERT INTO mf (gender) VALUES
('M'),('M'),('M'),('M'),('M'),('M'),('M'),('M'),
('M'),('M'),('M'),('M'),('F'),('F'),('M'),('M'),
('M'),('M'),('M'),('M'),('M'),('M'),('M'),('M'),
('M'),('M'),('M'),('M'),('M'),('M'),('M'),('M'),
('F'),('M'),('M'),('M'),('M'),('M'),('M'),('M');
ANALYZE TABLE mf;
EXPLAIN SELECT gender FROM mf WHERE gender='F';
EXPLAIN SELECT gender FROM mf WHERE gender='M';
EXPLAIN SELECT id FROM mf WHERE gender='F';
EXPLAIN SELECT id FROM mf WHERE gender='M';mysql> USE test
Database changed
mysql> DROP TABLE IF EXISTS mf;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE mf
-> (
-> id int not null auto_increment,
-> gender char(1),
-> primary key (id),
-> key (gender)
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.07 sec)
mysql> INSERT INTO mf (gender) VALUES
-> ('M'),('M'),('M'),('M'),('M'),('M'),('M'),('M'),
-> ('M'),('M'),('M'),('M'),('F'),('F'),('M'),('M'),
-> ('M'),('M'),('M'),('M'),('M'),('M'),('M'),('M'),
-> ('M'),('M'),('M'),('M'),('M'),('M'),('M'),('M'),
-> ('F'),('M'),('M'),('M'),('M'),('M'),('M'),('M');
Query OK, 40 rows affected (0.06 sec)
Records: 40 Duplicates: 0 Warnings: 0
mysql> ANALYZE TABLE mf;
+---------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------+---------+----------+----------+
| test.mf | analyze | status | OK |
+---------+---------+----------+----------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT gender FROM mf WHERE gender='F';
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
| 1 | SIMPLE | mf | ref | gender | gender | 2 | const | 3 | Using where; Using index |
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT gender FROM mf WHERE gender='M';
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
| 1 | SIMPLE | mf | ref | gender | gender | 2 | const | 37 | Using where; Using index |
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT id FROM mf WHERE gender='F';
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
| 1 | SIMPLE | mf | ref | gender | gender | 2 | const | 3 | Using where; Using index |
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT id FROM mf WHERE gender='M';
+----+-------------+-------+---mysql> USE test
Database changed
mysql> DROP TABLE IF EXISTS mf;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE mf
-> (
-> id int not null auto_increment,
-> gender char(1),
-> primary key (id),
-> key (gender)
-> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.05 sec)
mysql> INSERT INTO mf (gender) VALUES
-> ('M'),('M'),('M'),('M'),('M'),('M'),('M'),('M'),
-> ('M'),('M'),('M'),('M'),('F'),('F'),('M'),('M'),
-> ('M'),('M'),('M'),('M'),('M'),('M'),('M'),('M'),
-> ('M'),('M'),('M'),('M'),('M'),('M'),('M'),('M'),
-> ('F'),('M'),('M'),('M'),('M'),('M'),('M'),('M');
Query OK, 40 rows affected (0.00 sec)
Records: 40 Duplicates: 0 Warnings: 0
mysql> ANALYZE TABLE mf;
+---------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------+---------+----------+----------+
| test.mf | analyze | status | OK |
+---------+---------+----------+----------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT gender FROM mf WHERE gender='F';
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
| 1 | SIMPLE | mf | ref | gender | gender | 2 | const | 3 | Using where; Using index |
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT gender FROM mf WHERE gender='M';
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
| 1 | SIMPLE | mf | ref | gender | gender | 2 | const | 36 | Using where; Using index |
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT id FROM mf WHERE gender='F';
+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+
| 1 | SIMPLE | mf | ref | gender | gender | 2 | const | 3 | Using where |
+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT id FROM mf WHERE gender='M';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+Context
StackExchange Database Administrators Q#28592, answer score: 17
Revisions (0)
No revisions yet.