Very simple mysql query not using index

Question

Sorting my mySQL table does not use an index, and I don't know why.

I have:

CREATE TABLE IF NOT EXISTS `test` ( `a` int(11) NOT NULL, `b` int(11) NOT NULL, KEY `kk` (`a`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; 

and this:

 EXPLAIN SELECT * FROM test ORDER BY a 

as well as this

 EXPLAIN SELECT * FROM test USE INDEX ( kk ) ORDER BY a 

gives me the following:

 id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE test ALL NULL NULL NULL NULL 10009 Using filesort 

I would not want to see this file port and use the kk key to sort the table. What am I doing wrong?

---

Thanks for your posts guys, they answer my question! However, now I do not understand what is meant by "table scan" and "filesort"? Even if I select all the fields and all the rows of the table, is it faster to sort this table by one column by going to O (n) the internal index tree of this column (and then looking in the table to request additional columns in O (1) for each row = > does the index file store the physical position of each row in the table file or?) than to sort, for example, quickly sort in O (n * log n) (potentially) randomly stored rows in the table file without touching the index? I think my understanding of how indexes work in mySQL is wrong.

Answer 1

• You select all rows
• You select all columns

After what I said above, mysql evaluates its more efficient use of full scan.

To get it using an index, you need to add some WHERE , which would limit it to a reasonable number of rows returned (say 50)

Answer 2

@zerkms correctly, reading all the rows in the table, MySQL decides that he will have to read most of the table anyway, so there is no need to read the index. The optimizer changes behavior if you select a subset of the table.

For example, I created a table like yours and populated it with 16384 rows with random integers from 0 to 1,000,000. Then I tried EXPLAIN for different subsets of the table, first 15% of the table, then 17%, then 19%.

 mysql> EXPLAIN SELECT * FROM test where a < 150000 ORDER BY a; +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | test | range | kk | kk | 5 | NULL | 2272 | Using where | +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ mysql> EXPLAIN SELECT * FROM test where a < 170000 ORDER BY a; +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | test | range | kk | kk | 5 | NULL | 2560 | Using where | +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ mysql> EXPLAIN SELECT * FROM test where a < 190000 ORDER BY a; +----+-------------+-------+------+---------------+------+---------+------+-------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+-------+-----------------------------+ | 1 | SIMPLE | test | ALL | kk | NULL | NULL | NULL | 16384 | Using where; Using filesort | +----+-------------+-------+------+---------------+------+---------+------+-------+-----------------------------+ 

You can also convince him to use the index by reducing the columns until you just select the index columns. He decides to read the index one, and not touch the table. You can define an index with additional columns if you need, even if these columns are not needed for searching or sorting.

 mysql> ALTER TABLE test ADD KEY kk2 (a,b); mysql> EXPLAIN SELECT a,b FROM test ORDER BY a; +----+-------------+-------+-------+---------------+------+---------+------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+-------+-------------+ | 1 | SIMPLE | test | index | NULL | kk2 | 10 | NULL | 16384 | Using index | +----+-------------+-------+-------+---------------+------+---------+------+-------+-------------+ 

Answer 3

Since you do not have a WHERE clause, it will do fileort (table scan), unless the only element you select is an index. This query will use the index. See SQL Fiddle

 EXPLAIN SELECT a FROM test ORDER BY a 

However, if you select a column that is not in the index (* or b), it will scan the files. Either add a where clause with a coverage index, or change the selected columns.