Serious performance issue

ANALYZE provides better insights than EXPLAIN.

See the difference in speed and different index used below.

With FORCE INDEX(non_single_caches) update is much faster.

So the problem is in index intersection.

ANALYZE SELECT * FROM wp_wrc_caches WHERE cache_type='endpoint' AND object_type='post' AND is_single=0;
+------+-------------+---------------+------+------------------------------+-------------------+---------+-------------------+-------+---------+----------+------------+-----------------------+
| id   | select_type | table         | type | possible_keys                | key               | key_len | ref               | rows  | r_rows  | filtered | r_filtered | Extra                 |
+------+-------------+---------------+------+------------------------------+-------------------+---------+-------------------+-------+---------+----------+------------+-----------------------+
|    1 | SIMPLE      | wp_wrc_caches | ref  | cache_type,non_single_caches | non_single_caches | 809     | const,const,const | 11898 | 5950.00 |   100.00 |     100.00 | Using index condition |
+------+-------------+---------------+------+------------------------------+-------------------+---------+-------------------+-------+---------+----------+------------+-----------------------+
1 row in set (0.034 sec)

ANALYZE UPDATE wp_wrc_caches SET expiration='1970-01-01 00:00:01' WHERE cache_type='endpoint' AND object_type='post' AND is_single=0;
+------+-------------+---------------+-------------+------------------------------+------------------------------+---------+------+-------+---------+----------+------------+------------------------------------------------------------+
| id   | select_type | table         | type        | possible_keys                | key                          | key_len | ref  | rows  | r_rows  | filtered | r_filtered | Extra                                                      |
+------+-------------+---------------+-------------+------------------------------+------------------------------+---------+------+-------+---------+----------+------------+------------------------------------------------------------+
|    1 | SIMPLE      | wp_wrc_caches | index_merge | cache_type,non_single_caches | cache_type,non_single_caches | 42,809  | NULL | 11898 | 5950.00 |   100.00 |     100.00 | Using intersect(cache_type,non_single_caches); Using where |
+------+-------------+---------------+-------------+------------------------------+------------------------------+---------+------+-------+---------+----------+------------+------------------------------------------------------------+
1 row in set (3.128 sec)

ANALYZE UPDATE wp_wrc_caches IGNORE INDEX(cache_type) SET expiration='1970-01-01 00:00:01' WHERE cache_type='endpoint' AND object_type='post' AND is_single=0;
+------+-------------+---------------+-------+-------------------+-------------------+---------+------+-------+---------+----------+------------+-------------+
| id   | select_type | table         | type  | possible_keys     | key               | key_len | ref  | rows  | r_rows  | filtered | r_filtered | Extra       |
+------+-------------+---------------+-------+-------------------+-------------------+---------+------+-------+---------+----------+------------+-------------+
|    1 | SIMPLE      | wp_wrc_caches | range | non_single_caches | non_single_caches | 809     | NULL | 12524 | 5951.00 |   100.00 |     100.00 | Using where |
+------+-------------+---------------+-------+-------------------+-------------------+---------+------+-------+---------+----------+------------+-------------+
1 row in set (0.192 sec)

ANALYZE UPDATE wp_wrc_caches IGNORE INDEX(non_single_caches) SET expiration='1970-01-01 00:00:01' WHERE cache_type='endpoint' AND object_type='post' AND is_single=0;
+------+-------------+---------------+-------+---------------+---------+---------+------+--------+-----------+----------+------------+-------------+
| id   | select_type | table         | type  | possible_keys | key     | key_len | ref  | rows   | r_rows    | filtered | r_filtered | Extra       |
+------+-------------+---------------+-------+---------------+---------+---------+------+--------+-----------+----------+------------+-------------+
|    1 | SIMPLE      | wp_wrc_caches | index | cache_type    | PRIMARY | 8       | NULL | 120710 | 121915.00 |   100.00 |       4.88 | Using where |
+------+-------------+---------------+-------+---------------+---------+---------+------+--------+-----------+----------+------------+-------------+
1 row in set (0.270 sec)

ANALYZE UPDATE wp_wrc_caches FORCE INDEX(non_single_caches) SET expiration='1970-01-01 00:00:01' WHERE cache_type='endpoint' AND object_type='post' AND is_single=0;
+------+-------------+---------------+-------+-------------------+-------------------+---------+------+-------+---------+----------+------------+-------------+
| id   | select_type | table         | type  | possible_keys     | key               | key_len | ref  | rows  | r_rows  | filtered | r_filtered | Extra       |
+------+-------------+---------------+-------+-------------------+-------------------+---------+------+-------+---------+----------+------------+-------------+
|    1 | SIMPLE      | wp_wrc_caches | range | non_single_caches | non_single_caches | 809     | NULL | 11898 | 5950.00 |   100.00 |     100.00 | Using where |
+------+-------------+---------------+-------+-------------------+-------------------+---------+------+-------+---------+----------+------------+-------------+
1 row in set (0.197 sec)

• This reply was modified 1 year, 6 months ago by net.
• This reply was modified 1 year, 6 months ago by net.

The workaround is to replace the following code in function delete_object_type_caches in file wp-content/plugins/wp-rest-cache/includes/caching/class-caching.php:

$sql =
	"UPDATE `{$this->db_table_caches}`
		SET {$set_clause}
        WHERE `cache_type` = %s 
        AND `object_type` = %s
        AND `is_single` = %d";

with:

$sql =
	"UPDATE `{$this->db_table_caches}` FORCE INDEX(`non_single_caches`)
		SET {$set_clause}
        WHERE `cache_type` = %s 
        AND `object_type` = %s
        AND `is_single` = %d";

This issue is somewhat related to idea to get rid of database queries altogether by using the file system:
https://wordpress.org/support/topic/serving-static-json-files/

Actually, don’t really think that using force index is the solution.

The real question is why do we need it in the first place. For some reason MariaDB optimizer does not use the right index.

Probably removing cache_type index would help, but I guess that one is used for some other query?