Description:
If a table is created with:
create table t(a int, b int, KEY ((a=2*b)));
And filled only with few rows matching a=2*b,
The condition "where a=2*b" is not using functional index
The condition "(a=2*b)=1" uses the index.
How to repeat:
create table t(a int, b int, KEY ((a=2*b)));
INSERT INTO t (a,b) WITH RECURSIVE my_cte AS ( SELECT 1 AS n UNION ALL SELECT 1+n FROM my_cte WHERE n<900 ) SELECT n a, IF(RAND() < 0.01, n/2, n) b FROM my_cte;
select count(0) from t where a=2*b; -- not using index
select count(0) from t where (a=2*b)=1; -- index is used
mysql> select count(0) from t where a=2*b;
+----------+
| count(0) |
+----------+
| 4 |
+----------+
1 row in set (0.00 sec)
mysql> explain select count(0) from t where a=2*b;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t | NULL | ALL | NULL | NULL | NULL | NULL | 900 | 10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select count(0) from t where (a=2*b);
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t | NULL | ALL | NULL | NULL | NULL | NULL | 900 | 10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select count(0) from t where (a=2*b)=1;
+----+-------------+-------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | t | NULL | ref | functional_index | functional_index | 5 | const | 4 | 100.00 | NULL |
+----+-------------+-------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select count(0) from t where (a=2*b)=TRUE;
+----+-------------+-------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | t | NULL | ref | functional_index | functional_index | 5 | const | 4 | 100.00 | NULL |
+----+-------------+-------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
Suggested fix:
Use functional index with boolean expressions.
Unfortunately there is no BOOLEAN type in MySQL and (a=2*b) expression is defined as an integer column.
