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