Why is this simple MySQL query 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 the indices 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 try this simple choice:

select val from tbl where val = 'iii';

result: 86208 lines in a set (0.08 sec)

But when I want to change it a little:

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

result: 86208 lines in a set (47.30 sec)

I have a pointer to the right of the kum, which, where it indicates, all I change is a representation of the result lines. Why is there such a terrible delay? (I have to say that I cannot reproduce this delay every time I want: even after "reset the query cache" or setting the command "query_cache_type = off" this can be done quickly).

+5
source share
3 answers

, , . MySQL . . , val 10 6 , .

NOT val. . 250 , , val, id A LOT, . .

+3

ORDER BY `LIMIT. .

, , :

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

, . , id val.

0

All Articles