Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
10.0.1, 5.5.29, 5.5.30, 5.3.12
-
None
Description
Noticed only on MariaDB 5.5.29 and 5.5.30
Query is fine and using index on MySQL 5.0,5.1,5.5.
The index is not used when the value for the condition is retrieved from subquery (select max(id)...). The index is used when contant value is supplied.
How to reproduce.
MariaDB [test]> create table t1 (id int unsigned not null primary key, createdon timestamp default current_timestamp, name varchar(10)) engine=innodb; |
Query OK, 0 rows affected (0.01 sec) |
|
|
MariaDB [test]> insert into t1 (id) values (1),(2),(3),(4),(5),(6),(7),(8),(9); |
Query OK, 9 rows affected (0.00 sec) |
Records: 9 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [test]> update t1 set name=id; |
Query OK, 9 rows affected (0.00 sec) |
Rows matched: 9 Changed: 9 Warnings: 0 |
|
|
MariaDB [test]> explain select * from t1 where id>(select max(id) from t1)-1; |
+------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
+------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+ |
| 1 | PRIMARY | t1 | ALL | PRIMARY | NULL | NULL | NULL | 9 | Using where | |
| 2 | SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away | |
+------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+ |
2 rows in set (0.00 sec) |
|
|
MariaDB [test]> select max(id) from t1; |
+---------+ |
| max(id) | |
+---------+ |
| 9 |
|
+---------+ |
1 row in set (0.00 sec) |
|
|
MariaDB [test]> explain select * from t1 where id>9-1; |
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+ |
| 1 | SIMPLE | t1 | range | PRIMARY | PRIMARY | 4 | NULL | 1 | Using where | |
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+ |
1 row in set (0.00 sec) |