![]() ![]() ![]() There are 4M rows examined per second and for each row the LOWER function is called. There are 40k rows in user table and there are 100 QPS (queries per second) of this problematic query, then there are 40000*100 = 4M rows examined every second, which matches the 4M row reads per second in InnoDB status. Mysql > SELECT * FROM user WHERE LOWER(name) = 'E' Mysql > SELECT * FROM user WHERE name = 'E' Mysql > SELECT * FROM user WHERE name = 'e' The fact that the bad query is running a full index scan can be confirmed by selecting a column that is not covered by the index. MySQL thinks it will save IO by scanning a smaller index. It is actually a full table scan equivalent but on a secondary index with one fewer column in this case, which is known as full index scan. It is using the index as a covering index and performing a full scan on this secondary index instead of the primary clustered index. Notice that the possible keys is NULL while the key is ix_name, and rows is 7 instead of 1.Īlthough the query with LOWER is Using where Using index, it does not use the index to aid the lookup. That is, the named index covers the selected columns, so although it is not used to determine which rows to retrieve, an index scan is more efficient than a data row scan. This can happen if none of the possible_keys indexes are suitable for looking up rows, but all the columns selected by the query are columns of some other index. > It is possible for key to name an index that is not present in the possible_keys value. > If this column is NULL, there are no relevant indexes. But don’t be fooled.Īccording to MySQL Documentation EXPLAIN Output Format: Surprisingly, the query with LOWER is Using where Using index. | 1 | SIMPLE | user | NULL | index | NULL | ix_name | 32 | NULL | 7 | 100. Mysql > EXPLAIN SELECT id,name FROM user WHERE LOWER(name) = 'e' | 1 | SIMPLE | user | NULL | ref | ix_name | ix_name | 32 | const | 1 | 100. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | + -+-+-+-+-+-+-+-+-+-+-+-+ ![]() Mysql > EXPLAIN SELECT id,name FROM user WHERE name = 'e' ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |