CODEWITHSUNDEEP
mysql
Chris
Chris

Reputation: 1695

Can I force MySQL to use an index?

I created a table which has close to 800,000 records.

mysql> describe automation_search_test;
+----------+---------+------+-----+---------+----------------+
| Field    | Type    | Null | Key | Default | Extra          |
+----------+---------+------+-----+---------+----------------+
| id       | int(11) | NO   | PRI | NULL    | auto_increment | 
| filename | text    | YES  | MUL | NULL    |                | 
| site     | text    | YES  | MUL | NULL    |                | 
| script   | text    | YES  | MUL | NULL    |                | 
| station  | text    | YES  | MUL | NULL    |                | 
| result   | text    | YES  | MUL | NULL    |                | 
| failcode | text    | YES  | MUL | NULL    |                | 
| stbmodel | text    | YES  | MUL | NULL    |                | 
| rid      | text    | YES  | MUL | NULL    |                | 
| testdate | text    | YES  | MUL | NULL    |                | 
+----------+---------+------+-----+---------+----------------+

with an index on the filename column

mysql> show index from automation_search_test;
+------------------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table                  | Non_unique | Key_name      | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| automation_search_test |          0 | PRIMARY       |            1 | id          | A         |      767825 |     NULL | NULL   |      | BTREE      |         | 
| automation_search_test |          1 | ast_fname_idx |            1 | filename    | A         |      767825 |      255 | NULL   | YES  | BTREE      |         | 
+------------------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

With a WHERE condition, a query against the filename column uses the index without problem.

However, a simple SELECT query against the filename column ignores the index

mysql>  explain select filename from automation_search_test;
+----+-------------+------------------------+------+---------------+------+---------+------+--------+-------+
| id | select_type | table                  | type | possible_keys | key  | key_len | ref  | rows   | Extra |
+----+-------------+------------------------+------+---------------+------+---------+------+--------+-------+
|  1 | SIMPLE      | automation_search_test | ALL  | NULL          | NULL | NULL    | NULL | 767825 |       | 
+----+-------------+------------------------+------+---------------+------+---------+------+--------+-------+

How can I force the use of this index??

Upvotes: 1

Views: 144

Answers (2)

Mark Wilkins
Mark Wilkins

Reputation: 41232

It sounds as if you are looking for a covering index. A covering index (one that can satisfy the entire query without going to the table) only works if it contains the complete data. In your example, the index on filename includes up to 255 characters. If the actual file name were longer, it would not contain the entire data, so it is not a covering index for that query.

If filename had a type such as varchar(255), then it would use the index for the example query.

Upvotes: 2

Chris
Chris

Reputation: 17992

Since SELECT without a WHERE condition returns all of the values in that column, there is nothing for the index to do.

That's a bit like asking why the index at the back of a textbook isn't used when I want to read the whole book.

Upvotes: 3

Related Questions