Why is MySQL not using index for simple "SELECT * FROM Table WHERE field='value'&quot-CodePudding

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;

CodePudding user response：

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 the 5556677 value in quotes then the query uses the index and is almost instant.

CodePudding user response：

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:

For comparisons of a string column with a number, MySQL cannot use an index on the column to look up the value quickly. If str_col is an indexed string column, the index cannot be used when performing the lookup in the following statement:
SELECT * FROM tbl_name WHERE str_col=1;
The reason for this is that there are many different strings that may convert to the value 1, such as '1', ' 1', or '1a'.

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.

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       | ref  | idx_LogMessages_lotNumber | idx_LogMessages_lotNumber | 183     | const |    1 |   100.00 | NULL  |
 ---- ------------- ------------- ------------ ------ --------------------------- --------------------------- --------- ------- ------ ---------- -------

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:

mysql> explain SELECT text FROM LogMessages where lotNumber = 5556677 collate utf8mb4_unicode_ci;
 ---- ------------- ------------- ------------ ------ --------------------------- --------------------------- --------- ------- ------ ---------- ------- 
| id | select_type | table       | partitions | type | possible_keys             | key                       | key_len | ref   | rows | filtered | Extra |
 ---- ------------- ------------- ------------ ------ --------------------------- --------------------------- --------- ------- ------ ---------- ------- 
|  1 | SIMPLE      | LogMessages | NULL       | ref  | idx_LogMessages_lotNumber | idx_LogMessages_lotNumber | 183     | const |    1 |   100.00 | NULL  |
 ---- ------------- ------------- ------------ ------ --------------------------- --------------------------- --------- ------- ------ ---------- ------- 

mysql> explain SELECT text FROM LogMessages where lotNumber = cast(5556677 as char);
 ---- ------------- ------------- ------------ ------ --------------------------- --------------------------- --------- ------- ------ ---------- ------- 
| id | select_type | table       | partitions | type | possible_keys             | key                       | key_len | ref   | rows | filtered | Extra |
 ---- ------------- ------------- ------------ ------ --------------------------- --------------------------- --------- ------- ------ ---------- ------- 
|  1 | SIMPLE      | LogMessages | NULL       | ref  | idx_LogMessages_lotNumber | idx_LogMessages_lotNumber | 183     | const |    1 |   100.00 | NULL  |
 ---- ------------- ------------- ------------ ------ --------------------------- --------------------------- --------- ------- ------ ---------- ------- 

mysql> explain SELECT text FROM LogMessages where lotNumber = concat(5556677);
 ---- ------------- ------------- ------------ ------ --------------------------- --------------------------- --------- ------- ------ ---------- ------- 
| id | select_type | table       | partitions | type | possible_keys             | key                       | key_len | ref   | rows | filtered | Extra |
 ---- ------------- ------------- ------------ ------ --------------------------- --------------------------- --------- ------- ------ ---------- ------- 
|  1 | SIMPLE      | LogMessages | NULL       | ref  | idx_LogMessages_lotNumber | idx_LogMessages_lotNumber | 183     | const |    1 |   100.00 | NULL  |
 ---- ------------- ------------- ------------ ------ --------------------------- --------------------------- --------- ------- ------ ---------- -------

In these three examples, type: ref indicates it's using the index, doing a non-unique lookup.