WordPress Popular Posts WPP

SELECT ID FROM wp_posts WHERE guid RLIKE '/uploads/2022/07/xxx.jpg';

mysql> EXPLAIN SELECT ID FROM wp_posts WHERE guid RLIKE '/uploads/2022/07/xxx.jpg';
+----+-------------+----------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+----+-------------+----------+------+---------------+------+---------+------+-------+-------------+
|  1 | SIMPLE      | wp_posts | ALL  | NULL          | NULL | NULL    | NULL | 39608 | Using where |
+----+-------------+----------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE wp_posts ADD INDEX guid (guid);

mysql> EXPLAIN SELECT ID FROM wp_posts WHERE guid RLIKE '/uploads/2022/07/xxx.jpg';
+----+-------------+----------+-------+---------------+------+---------+------+-------+--------------------------+
| id | select_type | table    | type  | possible_keys | key  | key_len | ref  | rows  | Extra                    |
+----+-------------+----------+-------+---------------+------+---------+------+-------+--------------------------+
|  1 | SIMPLE      | wp_posts | index | NULL          | guid | 1022    | NULL | 39608 | Using where; Using index |
+----+-------------+----------+-------+---------------+------+---------+------+-------+--------------------------+
1 row in set (0.00 sec)

mysql> DROP INDEX guid ON wp_posts;

type
index・・・フルインデックススキャン。インデックス全体をスキャンする必要があるのでとても遅い。
漢(オトコ)のコンピュータ道: MySQLのEXPLAINを徹底解説!! 2009