1

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.

  • Please care about the readability and formatting of your questions. There's a WYSIWYG editor helping you to do that. – kaiser Mar 2 '14 at 13:17
  • Have you tried running the same query directly inside phpMyAdmin or Adminer? The problem might be that you're doing a query inside a query, but you can only test that. The other question you should ask yourself is where the query comes from and if you couldn't do it more straight forward. – kaiser Mar 2 '14 at 13:21
  • First thank you for the formatting, I was in a hurry still should have taken the time for that! Also you were right, I removed the nested query and it was lightning fast in phpmyadmin. The query comes from the option from my theme to not display certain categories on a page. I'm not quite sure how I can work around it but I will look into it for some time and update here, if I fix the problem. Again thank you for your efforts! – Johannes Ruof Mar 2 '14 at 13:29
  • 1
    I was able to resolve the issue, however couldn't post my solution as an answer here due to my low reputation I can only do so in 8 hours. Until then it was solved by using category__in (array) instead of category__not_in (array). I will post the detailed answer with the new debug output as soon as I can! Thank you kaiser for helping me out. – Johannes Ruof Mar 2 '14 at 13:49
  • Nice to see that the problem is gone. The restriction with 8 hours is there for everyone. Even we moderators would have to wait that time :) – kaiser Mar 2 '14 at 14:59
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.