MySql Query very slow

Question

I have a problem with a query on my website. When I load the page it is incredibly slow and only after over 12 seconds the content starts showing. I have installed a debug plugin and found the query that takes so long. The output of the plugin is the following:

Time: 12,315.2ms (12.315196037292s)

Query:

SELECT SQL_CALC_FOUND_ROWS meruca_posts.ID 
FROM meruca_posts 
WHERE 1=1 
AND (
    meruca_posts.ID NOT IN (
        SELECT object_id
        FROM meruca_term_relationships
        WHERE term_taxonomy_id IN (14)
    )
)
AND meruca_posts.post_type = 'post' 
AND (
    meruca_posts.post_status = 'publish' 
    OR meruca_posts.post_status = 'private'
)
GROUP BY meruca_posts.ID ORDER BY meruca_posts.post_date DESC LIMIT 0, 10

Function: WP_Query->get_posts() (meruca_ is my table prefix)

I have only basic understanding of mysql and can't really see, what may cause this to take so long. If anybody has an idea what I could change it would be much appreciated. There are about 5k posts on the site right now which isn't too many and it is running on a dedicated server that should have much more than sufficient resourced for any query theoretically.

Answer 1

You must use a temporary table and an alias:

SELECT SQL_CALC_FOUND_ROWS * 
FROM meruca_posts 
WHERE ID NOT IN (SELECT ID 
            FROM (
                    SELECT object_id as ID
                    FROM meruca_term_relationships
                    WHERE term_taxonomy_id IN (14)
                )
            AS temp_object)
AND post_type = 'post' 
AND post_status IN ('publish','private') 
GROUP BY ID ORDER BY post_date DESC LIMIT 0, 10