Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
N/A
Description
I expect the index to be used in this case, but it isn't:
MariaDB [test]> CREATE TABLE t (a VARCHAR(10), b VARCHAR(10), INDEX idx_a (a)); |
Query OK, 0 rows affected (0.02 sec) |
|
MariaDB [test]> INSERT INTO t VALUES ('1', '1'), ('2', '2'), ('3', '3'), ('4', '4'), ('5', '5'), ('1', '5'); |
Query OK, 6 rows affected (0.01 sec) |
Records: 6 Duplicates: 0 Warnings: 0
|
|
MariaDB [test]> EXPLAIN SELECT * FROM t WHERE a = 3 \G |
*************************** 1. row ***************************
|
id: 1
|
select_type: SIMPLE
|
table: t |
type: ALL |
possible_keys: idx_a
|
key: NULL |
key_len: NULL |
ref: NULL |
rows: 6 |
Extra: Using where |
1 row in set (0.00 sec) |
Why do I expect that? Because an index is used if the query implies the opposite conversion (int to varchar):
MariaDB [test]> CREATE TABLE t (a INT UNSIGNED, b INT UNSIGNED, INDEX idx_a (a)); |
Query OK, 0 rows affected (0.01 sec) |
MariaDB [test]> INSERT INTO t VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5), (1, 5); |
Query OK, 6 rows affected (0.00 sec) |
Records: 6 Duplicates: 0 Warnings: 0
|
|
MariaDB [test]> EXPLAIN SELECT * FROM t WHERE a = '3' \G |
*************************** 1. row ***************************
|
id: 1
|
select_type: SIMPLE
|
table: t |
type: ref
|
possible_keys: idx_a
|
key: idx_a |
key_len: 5
|
ref: const
|
rows: 1 |
Extra:
|
1 row in set (0.00 sec) |
Bug found on 10.2, test ran on 10.0.
I did not check MySQL.