CODEWITHSUNDEEP
mysqlperformanceindexingwhere-clause
Igor Shalyminov
Igor Shalyminov

Reputation: 704

Why this simple MySQL query is so slow?

So here is a very simple table 'tbl':

+---------+---------------------+------+-----+---------+----------------+
| Field   | Type                | Null | Key | Default | Extra          |
+---------+---------------------+------+-----+---------+----------------+
| val     | varchar(45)         | YES  | MUL | NULL    |                |
| id      | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
+---------+---------------------+------+-----+---------+----------------+

And indexes for it:

+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| tbl    |          0 | PRIMARY  |            1 | id          | A         |   201826018 |     NULL | NULL   |      | BTREE      |         |
| tbl    |          1 | val      |            1 | val         | A         |      881336 |     NULL | NULL   | YES  | BTREE      |         |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

I'm trying this simple select:

select val from tbl where val = 'iii';

result: 86208 rows in set (0.08 sec)

But when I want to slightly modify it:

select id, val from tbl where val = 'iii';

the result is: 86208 rows in set (47.30 sec)

I have an index right on the coumn that where points to, all I'm modifying is the result rows representation. Why there is so terrifying delay? (I have to say that I can't reproduce this delay every time I want: even after 'reset query cache' or setting 'query_cache_type=off' command it can be done quickly).

Upvotes: 5

Views: 258

Answers (3)

Jim Garrison
Jim Garrison

Reputation: 86754

Without actually examining your server configuration it is hard to tell, but here's an educated guess. In the first instance, MySQL is able to satisfy your query without actually reading the table data. All the information you have asked for can be retrieved from the index alone. Notice that the cardinality of the val index is only on the order of 106 rows, and the rows are going to be very short in the index.

In the second case you have asked for data NOT in the index on val. Now the engine has to actually find and read rows from the data. Here the cardinality is about 250 times larger, and since the index will retrieve rows ordered by val, finding the corresponding id values will require A LOT of jumping around in several hundred gigs of data on disk. This is going to be very much slower.

Upvotes: 3

kba
kba

Reputation: 19466

You're doing a select based on two columns, but no index of both exists. Try adding a new index consisting of both id and val.

Upvotes: 0

Wolph
Wolph

Reputation: 80011

Try to add a ORDER BY and `LIMIT to the query. That should help a lot.

I think if you change the query to this it will be faster:

select id, val from tbl where val = 'iii' order by val limit 10;

Upvotes: 0

Related Questions