I’m currently inspecting a WordPress setup with multiple custom post types that should be included into search results.
The database query for a search after the word Search looks like this:
SELECT SQL_CALC_FOUND_ROWS
wp_posts.ID
FROM
wp_posts
WHERE
1=1
AND
(
(
(wp_posts.post_title LIKE '%Search%')
OR (wp_posts.post_content LIKE '%Serach%')
)
)
AND
wp_posts.post_type IN ('post', 'page', 'attachment', 'abbr', 'date', 'event', 'jobs', 'special', 'action', 'poll')
AND
(
wp_posts.post_status="publish"
OR
wp_posts.post_author = 28
AND
wp_posts.post_status="private"
)
ORDER BY
wp_posts.post_title LIKE '%Search%' DESC,
wp_posts.post_date DESC
LIMIT 0, 16
Here’s the EXPLAIN result for this query:
+----+-------------+----------+------+------------------------------+------+---------+------+--------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+------------------------------+------+---------+------+--------+-----------------------------+
| 1 | SIMPLE | ae_posts | ALL | type_status_date,post_author | NULL | NULL | NULL | 338315 | Using where; Using filesort |
+----+-------------+----------+------+------------------------------+------+---------+------+--------+-----------------------------+
The query runs about 5 to sometimes 8 seconds.
I played around a bit and found that MySQL uses the index type_status_date when the post_type IN () clause is changed to post_type="" which of course is not a solution but it brought me to the idea to hint the usage of this key like this:
SELECT SQL_CALC_FOUND_ROWS
wp_posts.ID
FROM
wp_posts
FORCE INDEX
( type_status_date )
WHERE
1=1
//...
Which reduces the runtime at least to a third of the original value. Here’s the EXPLAIN result:
+----+-------------+----------+-------+------------------+------------------+---------+------+--------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+-------+------------------+------------------+---------+------+--------+-----------------------------+
| 1 | SIMPLE | ae_posts | range | type_status_date | type_status_date | 164 | NULL | 145573 | Using where; Using filesort |
+----+-------------+----------+-------+------------------+------------------+---------+------+--------+-----------------------------+
The questions are: is it basically a good idea to force the usage of an index like this? Are there any possible negative side-effects? Is it typical for this type of query to ignore the index type_status_date or might there are some configuration issues on the server?
