Expected that index usage for like "DATE(col) = const" to "col BETWEEN concat(const, ' 00:00:00') AND concat(const, ' 23:59:59')" is the same for SELECT, UPDATE and DELETE, but it works only for SELECT.
Example test:
create table t1 (pk int primary key, a datetime, c int, key(a));
|
|
INSERT INTO t1 (pk,a,c) VALUES (1,'2009-11-29 13:43:32', 2);
|
INSERT INTO t1 (pk,a,c) VALUES (2,'2009-11-29 03:23:32', 2);
|
INSERT INTO t1 (pk,a,c) VALUES (3,'2009-10-16 05:56:32', 2);
|
INSERT INTO t1 (pk,a,c) VALUES (4,'2010-11-29 13:43:32', 2);
|
INSERT INTO t1 (pk,a,c) VALUES (5,'2010-10-16 05:56:32', 2);
|
INSERT INTO t1 (pk,a,c) VALUES (6,'2011-11-29 13:43:32', 2);
|
INSERT INTO t1 (pk,a,c) VALUES (7,'2012-10-16 05:56:32', 2);
|
|
explain format=json select * from t1 where year(a) = 2010;
|
explain format=json update t1 set c = 0 where year(a) = 2010;
|
explain format=json delete from t1 where year(a) = 2010;
|
|
drop table t1;
|
Actual result:
explain format=json select * from t1 where year(a) = 2010;
|
EXPLAIN
|
{
|
"query_block": {
|
"select_id": 1,
|
"cost": 0.003808422,
|
"nested_loop": [
|
{
|
"table": {
|
"table_name": "t1",
|
"access_type": "range",
|
"possible_keys": ["a"],
|
"key": "a",
|
"key_length": "6",
|
"used_key_parts": ["a"],
|
"loops": 1,
|
"rows": 2,
|
"cost": 0.003808422,
|
"filtered": 100,
|
"index_condition": "t1.a between '2010-01-01 00:00:00' and '2010-12-31 23:59:59'"
|
}
|
}
|
]
|
}
|
}
|
explain format=json update t1 set c = 0 where year(a) = 2010;
|
EXPLAIN
|
{
|
"query_block": {
|
"select_id": 1,
|
"table": {
|
"update": 1,
|
"table_name": "t1",
|
"access_type": "ALL",
|
"rows": 7,
|
"attached_condition": "year(t1.a) = 2010"
|
}
|
}
|
}
|
explain format=json delete from t1 where year(a) = 2010;
|
EXPLAIN
|
{
|
"query_block": {
|
"select_id": 1,
|
"table": {
|
"delete": 1,
|
"table_name": "t1",
|
"access_type": "ALL",
|
"rows": 7,
|
"attached_condition": "year(t1.a) = 2010"
|
}
|
}
|
}
|
Bug
Major
MDEV-8320 Allow index usage for DATE(datetime_column) = const
