Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Minor
-
Resolution: Unresolved
-
5.5, 10.0, 10.1
-
Ubuntu 14.04
Description
PK is used for a simple lookup by number literal:
MariaDB [music]> EXPLAIN EXTENDED SELECT * FROM items WHERE id = 2300103499779; |
+------+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | |
+------+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------+ |
| 1 | SIMPLE | items | const | PRIMARY | PRIMARY | 8 | const | 1 | 100.00 | | |
+------+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------+ |
PK is also used for a simple lookup by string literal:
MariaDB [music]> EXPLAIN EXTENDED SELECT * FROM items WHERE id = '2300103499779'; |
+------+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | |
+------+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------+ |
| 1 | SIMPLE | items | const | PRIMARY | PRIMARY | 8 | const | 1 | 100.00 | | |
+------+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------+ |
PK is not used when using a mix of string and number literals:
MariaDB [music]> EXPLAIN EXTENDED SELECT * FROM items WHERE id IN ('2300103499779', 2300103499779); |
+------+-------------+-------+------+---------------+------+---------+------+----------+----------+-------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | |
+------+-------------+-------+------+---------------+------+---------+------+----------+----------+-------------+ |
| 1 | SIMPLE | items | ALL | PRIMARY | NULL | NULL | NULL | 16060958 | 100.00 | Using where | |
+------+-------------+-------+------+---------------+------+---------+------+----------+----------+-------------+ |