Details
-
Task
-
Resolution: Done
-
Major
-
SaaS, 2.12.0
-
False
-
None
-
False
-
Not Started
-
Not Started
-
Not Started
-
Not Started
-
Not Started
-
Not Started
Description
Currently query to get accounts so that they are scheduled for indexing confuses MySQL and it uses wrong index. Getting batches of 1000 accounts take between 3 and 8 seconds instead of milliseconds when PRIMARY index is used. First is original slow query and second is one that MySQL optimizes well.
explain SELECT `accounts`.`id` FROM `accounts` WHERE (`accounts`.`master` = 0 OR `accounts`.`master` IS NULL) AND (`accounts`.`state` != 'scheduled_for_deletion') AND NOT EXISTS (SELECT * FROM `accounts` acc2 WHERE `accounts`.`provider_account_id` = acc2.`id` AND acc2.`state` = 'scheduled_for_deletion') AND (`accounts`.`id` > 2445582715687) ORDER BY `accounts`.`id` ASC LIMIT 1000; +----+--------------------+----------+------------+-------------+-------------------------------------------------------------------------------+--------------------------+---------+------------------------------------------------+---------+----------+----------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+----------+------------+-------------+-------------------------------------------------------------------------------+--------------------------+---------+------------------------------------------------+---------+----------+----------------------------------------------------+ | 1 | PRIMARY | accounts | NULL | ref_or_null | PRIMARY,index_accounts_on_master,index_accounts_on_state_and_state_changed_at | index_accounts_on_master | 2 | const | 1011927 | 25.37 | Using index condition; Using where; Using filesort | | 2 | DEPENDENT SUBQUERY | acc2 | NULL | eq_ref | PRIMARY,index_accounts_on_state_and_state_changed_at | PRIMARY | 8 | system_enterprise.accounts.provider_account_id | 1 | 5.00 | Using where | +----+--------------------+----------+------------+-------------+-------------------------------------------------------------------------------+--------------------------+---------+------------------------------------------------+---------+----------+----------------------------------------------------+ 2 rows in set, 2 warnings (0.00 sec) explain SELECT `accounts`.`id` FROM `accounts` WHERE (`accounts`.`master` != 1 OR `accounts`.`master` IS NULL) AND (`accounts`.`state` != 'scheduled_for_deletion') AND NOT EXISTS (SELECT * FROM `accounts` acc2 WHERE `accounts`.`provider_account_id` = acc2.`id` AND acc2.`state` = 'scheduled_for_deletion') AND (`accounts`.`id` > 2445582715687) ORDER BY `accounts`.`id` ASC LIMIT 1000; +----+--------------------+----------+------------+--------+-------------------------------------------------------------------------------+---------+---------+------------------------------------------------+---------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+----------+------------+--------+-------------------------------------------------------------------------------+---------+---------+------------------------------------------------+---------+----------+-------------+ | 1 | PRIMARY | accounts | NULL | range | PRIMARY,index_accounts_on_master,index_accounts_on_state_and_state_changed_at | PRIMARY | 8 | NULL | 1011926 | 25.37 | Using where | | 2 | DEPENDENT SUBQUERY | acc2 | NULL | eq_ref | PRIMARY,index_accounts_on_state_and_state_changed_at | PRIMARY | 8 | system_enterprise.accounts.provider_account_id | 1 | 5.00 | Using where | +----+--------------------+----------+------------+--------+-------------------------------------------------------------------------------+---------+---------+------------------------------------------------+---------+----------+-------------+ 2 rows in set, 2 warnings (0.00 sec)
That is, for an unknown reason, MySQL optimizes well when master != 1 OR master IS NULL while it f%^s up when it is master = 0 OR master IS NULL
when using the latter, it decides to use index on master column but that can't help with ordering so it end up very slow.
We can only hope that this solution remains valid for future versions of mysql.
Attachments
Issue Links
- clones
-
-
- Closed
-
- relates to
-
THREESCALE-8511 Enqueue Accounts faster
-
- Closed
-
- mentioned on
