Open
Description
After fixing #819, we started to use LOWER()
in our queries and makes existing indexes unusable:
mysql> explain SELECT c.id FROM countries c LEFT JOIN countries_aliases ca ON ca.country_id = c.id WHERE LOWER(c.name) IN ('Test') OR LOWER(c.name_ru) IN ('Test') OR LOWER(ca.name) IN ('Test') OR LOWER(ca.name_ru) IN ('Test');
+----+-------------+-------+------------+------+---------------------------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------------------------+------+---------+------+------+----------+----------------------------------------------------+
| 1 | SIMPLE | c | NULL | ALL | NULL | NULL | NULL | NULL | 52 | 100.00 | NULL |
| 1 | SIMPLE | ca | NULL | ALL | fk_countries_aliases_country_id | NULL | NULL | NULL | 1 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------------------------+------+---------+------+------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)
mysql> show warnings;
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1003 | /* select#1 */ select `mystamps`.`c`.`id` AS `id` from `mystamps`.`countries` `c` left join `mystamps`.`countries_aliases` `ca` on((`mystamps`.`ca`.`country_id` = `mystamps`.`c`.`id`)) where ((lower(`mystamps`.`c`.`name`) = 'Test') or (lower(`mystamps`.`c`.`name_ru`) = 'Test') or (lower(`mystamps`.`ca`.`name`) = 'Test') or (lower(`mystamps`.`ca`.`name_ru`) = 'Test')) |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
The same happens for categories.
This regression found after question from @asm0dey.