Differential D5295
Speed up syncroton_content cleanup queries with an index Authored by mollekopf on Jun 2 2025, 3:28 PM.
Details
We have a lot of slow queries like this: DELETE FROM `syncroton_content` WHERE `device_id` = '...' AND `folder_id` = '...' AND `is_deleted` = 1; These queries sometimes scan over ~240k entries (all entries
Diff Detail
Event TimelineComment Actions Did you test whether it indeed does sped up the query?
Comment Actions Seems to work: MariaDB [roundcube]> ANALYZE SELECT * FROM `syncroton_content` WHERE `device_id` = 'b64bcab5169887ba3318728b5174be9958cf3a00' AND `folder_id` = '75e3e4a5c192280e5d6f62281453edbeb9d8252b' AND `is_deleted` = 1; +------+-------------+-------------------+------+---------------------------------------------------------------+------+---------+------+-------+----------+----------+------------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-------------+-------------------+------+---------------------------------------------------------------+------+---------+------+-------+----------+----------+------------+-------------+ | 1 | SIMPLE | syncroton_content | ALL | device_id--folder_id--contentid,syncroton_contents::device_id | NULL | NULL | NULL | 18487 | 17559.00 | 50.00 | 0.00 | Using where | +------+-------------+-------------------+------+---------------------------------------------------------------+------+---------+------+-------+----------+----------+------------+-------------+ 1 row in set (0.011 sec) MariaDB [roundcube]> CREATE INDEX `is_deleted` ON `syncroton_content`(`is_deleted`); Query OK, 0 rows affected (0.017 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [roundcube]> ANALYZE SELECT * FROM `syncroton_content` WHERE `device_id` = 'b64bcab5169887ba3318728b5174be9958cf3a00' AND `folder_id` = '75e3e4a5c192280e5d6f62281453edbeb9d8252b' AND `is_deleted` = 1; +------+-------------+-------------------+------+--------------------------------------------------------------------------+------------+---------+-------+------+--------+----------+------------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-------------+-------------------+------+--------------------------------------------------------------------------+------------+---------+-------+------+--------+----------+------------+-------------+ | 1 | SIMPLE | syncroton_content | ref | device_id--folder_id--contentid,syncroton_contents::device_id,is_deleted | is_deleted | 2 | const | 1 | 0.00 | 50.00 | 100.00 | Using where | +------+-------------+-------------------+------+--------------------------------------------------------------------------+------------+---------+-------+------+--------+----------+------------+-------------+ 1 row in set (0.001 sec)
Comment Actions It surprises me that no key at all is used here. I'm also wondering whether replacing both KEY (is_deleted), KEY (device_id) with a single KEY (device_id, is_deleted) would make a difference. Probably not, if there's only 50 deleted records in the whole table. Comment Actions BTW, we have the same situation in syncroton_folder table. The table is smaller, so it probably is not a problem, but we should use the same index there.
Comment Actions Let's try with replacing the index then... Should work, just find it a bit difficult to test if the index is used in the same fashion. | ||||||||||||||||||||||||||||||||