Skip to content

Consider adding indexes for category and country in lower case #828

Open
@php-coder

Description

@php-coder

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.

Metadata

Metadata

Assignees

No one assigned

    Labels

    area/optimizationThis change could speed up our site

    Projects

    No projects

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions