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).
source
share