4

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?

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.