Very simple mysql query not using index

Question

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

I've got:

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 this:

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'd like not to see this filesort, and use the key kk to sort my table. What am I doing wrong?

Thank you for your posts guys, they answer my question! However, now I do not undestand what is meant by "table scan" and "filesort"? Even if I am selecting all fields and all rows of a table, isn't it faster to sort that table by one column by walking in O(n) the internal tree of the index of that column (and then looking up in the table file the extra columns requested, in O(1) for each row => the index file stores each row's physical position in the table file, or?), than to sort e.g. by quick sort in O(n * log n) the (potentially) randomly stored rows in the table file, without touching the index? I guess my understanding of how indexes work in mySQL is wrong.

zerkms · Accepted Answer · 2013-07-21 10:21:26Z

22

You're selecting all the rows
You're selecting all columns

Following what I said above - mysql estimates it to be more efficient to use full scan.

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

edited Jul 21, 2013 at 10:21

answered Jul 18, 2013 at 0:26

zerkms

256k73 gold badges447 silver badges551 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

zerkms Over a year ago

@a_horse_with_no_name: I probably meant "the whole table" (or "all the rows" indeed). Not sure how it express in English in a correct way.

user330315 Over a year ago

Yes, "the whole table" or "all the rows" would be better I think.

Charles Wood Over a year ago

Should I "AND" #1 and #2 or "OR" them?

zerkms Over a year ago

@CharlesWood it's both, so "and"

Bill Karwin · Accepted Answer · 2013-07-18 00:36:16Z

@zerkms is correct, by reading all the rows in the table, MySQL decides it's going to have to read the majority of the table anyway so there's no need to read the index as well. The optimizer changes behavior if you select a subset of the table.

For example, I created a table like yours and filled it with 16384 rows, with random integers between 0 and 1000000. 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 it to use the index by decreasing the columns until you are just selecting the columns of the index. It'll decide to read the index alone, and not touch the table. You can define an index with extra columns if you need to, even if those 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 |
+----+-------------+-------+-------+---------------+------+---------+------+-------+-------------+

kheld · Accepted Answer · 2013-07-18 00:31:56Z

2

Since you have no WHERE clause it will do a filesort (table scan) unless the only item you are selecting is from the index. This query will use the index. See this SQL Fiddle

EXPLAIN SELECT a FROM test ORDER BY a

However if you select a column not in the index (* or b) it will do a file scan. Either add a where clause with a covering index or change the columns you are selecting.

answered Jul 18, 2013 at 0:31

kheld

7925 silver badges14 bronze badges

Collectives™ on Stack Overflow

Very simple mysql query not using index

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related