I’m trying to make a very simple query to a MySQL 5.7 database but the query is slow and the explain shows it is not using the index, although it lists it as a possible key. Below is the query, explain output, and table schema. Any ideas? Thanks
Query: SELECT text FROM LogMessages where lotNumber = 5556677
Explain output:
mysql> explain SELECT text FROM LogMessages where lotNumber = 5556677;
+----+-------------+------------------------------+------------+------+------------------------------------------------------------------------------+------+---------+------+----------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------------------------+------------+------+------------------------------------------------------------------------------+------+---------+------+----------+----------+-------------+
| 1 | SIMPLE | LogMessages | NULL | ALL | idx_LogMessages_lotNumber | NULL | NULL | NULL | 35086603 | 10.00 | Using where |
+----+-------------+------------------------------+------------+------+------------------------------------------------------------------------------+------+---------+------+----------+----------+-------------+
1 row in set, 5 warnings (0.07 sec)
Table schema:
CREATE TABLE `LogMessages` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`lotNumber` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`text` text COLLATE utf8mb4_unicode_ci,
PRIMARY KEY (`id`),
UNIQUE KEY `idLogMessages_UNIQUE` (`id`),
KEY `idx_LogMessages_lotNumber` (`lotNumber`)
) ENGINE=InnoDB AUTO_INCREMENT=37545325 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
2
Answers
Ah, just figured it out. The
lotNumber
field is a varchar but I'm typing it in as an integer in the query. If I put the5556677
value in quotes then the query uses the index and is almost instant.You already got the answer, but I thought I’d give some more context.
https://dev.mysql.com/doc/refman/5.7/en/type-conversion.html explains why the index is not used:
The EXPLAIN report in your question shows
type: ALL
which means it’s a table-scan. It’s not using the index.If we were to use a string literal, it’s a string-to-string comparison, so it uses the index.
It also uses the index if we use a numeric literal in an expression that evaluates to a string value. There are a few ways to do this:
In these three examples,
type: ref
indicates it’s using the index, doing a non-unique lookup.