WordPress.org

1. mikeo75
   Member
   Posted 3 years ago #

   My host shows our site being problematic when it comes to the particular query below. It takes 2-3 seconds to complete.

   Any ideas on how to speed this up? Do we have a DB that's just too big on these three tables?

   Created 06:55 PM 07/28/2008

   ### 74 Queries
   ### Total time: 154, Average time: 2.08108108108108
   ### Taking 2 to 3 seconds to complete
   ### Rows analyzed 55576 - 56375
   SELECT t.*, tt.*
   FROM wp_terms AS t
   INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id
   INNER JOIN wp_term_relationships AS tr ON tt.term_taxonomy_id = tr.term_taxonomy_id
   INNER JOIN wp_posts AS p ON tr.object_id = p.ID
   WHERE tt.taxonomy IN ( 'XXX' )
   AND p.post_date_gmt < 'XXX'

   AND tt.count > XXX

   GROUP BY t.term_id
   ORDER BY tt.count DESC
   LIMIT XXX;

   SELECT t.*, tt.*
   FROM wp_terms AS t
   INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id
   INNER JOIN wp_term_relationships AS tr ON tt.term_taxonomy_id = tr.term_taxonomy_id
   INNER JOIN wp_posts AS p ON tr.object_id = p.ID
   WHERE tt.taxonomy IN ( 'post_tag' )
   AND p.post_date_gmt < '2008-07-28 17:49:23'

   AND tt.count > 0

   GROUP BY t.term_id
   ORDER BY tt.count DESC
   LIMIT 45;

2. mikeo75
   Member
   Posted 3 years ago #

   Anyone?

3. Ogre
   Member
   Posted 3 years ago #

   You must have a lot of different slugs and/or categories. That's the only reason I can see for the query to take so long. I ran the query on my server, but because of the limited amount of data it took no time to run: "Showing rows 0 - 3 (4 total, Query took 0.0002 sec)".

   You might change this:

   WHERE tt.taxonomy IN ( 'post_tag' )

   To

   WHERE tt.taxonomy='post_tag'

   And see if you get any improvement.

4. Ogre
   Member
   Posted 3 years ago #

   Ask your host to enable query caching. It can make a HUGE difference.

5. mikeo75
   Member
   Posted 3 years ago #

   This is what I see for query caching:

   # query cache
   query_cache_limit = 1048576
   query_cache_size = 12M
   query_cache_type = 1

   Also, changing that query you said, it will just get wiped out in the next release I assume.

   Not even sure what file that is in :)

6. Ogre
   Member
   Posted 3 years ago #

   You should get a slight improvement if you index wp_taxonomy.taxonomy. If you have phpmyadmin, adding this index is simple.

   If you can get "slow query logging" enabled hand have access to the log, it should point out where you can make improvements. The taxonomy column was the query item I could spot that was not indexed properly.

7. Ogre
   Member
   Posted 3 years ago #

   Some explanation on how to optimize a query.

   Take the query giving you problems and run it with the word 'EXPLAIN' prepended, e.g. "EXPLAIN SELECT t.*,tt.* FROM ...".

   What you get is a table explaining how the query was handled. Look at the column called "key". If any of them have "null", that's bad. Also look at the column called "rows". A big number is bad. Several rows with big numbers are very bad. Multiply all the values in "rows" and that's the number of rows your query had to look at to come up with the result. A badly written query could have looked at millions of rows (it can actually be more rows than you have in your database because the same row could be examined multiple times!).

   The trick (and this is were a good DBA earns his big bucks) is to reduce the number of rows looked at without affecting the query. First off, get rid of the "null" in the "key" column by indexing the taxonomy column. After that, it's a matter of rewriting the query, which is a bit beyond my skillset (I'm just a linux system admin) for a query this complicated.

8. beisbolct
   Member
   Posted 3 years ago #

   how do you do the index...

   index wp_taxonomy.taxonomy

   i am having the same problem on mine as well.

9. Ogre
   Member
   Posted 3 years ago #

   To add an index via phpmyadmin, select the table you need to modify, find the the column name in the list, look on the far right for the 'action' that adds an index and click it. On my system with a very small table it's done almost instantly. If you have a lot of data in the table it might take a few seconds.

   From within a mysql shell, you can add the table with this command:

   ALTER TABLE wp_term_taxonomy ADD INDEX ( 'taxonomy' );

10. Ogre
    Member
    Posted 3 years ago #

    Oops, no quotes around the word in the parens. My bad.

11. mikeo75
    Member
    Posted 3 years ago #

    Good stuff Ogre, I appreciate the help...I'm going to try and run the EXPLAIN thing on this query and see what happens.

12. Ogre
    Member
    Posted 3 years ago #

    I'm curious about which plugin is executing this query. The author needs to be educated a small amount on the dangers of querying on unindexed columns. It would be very easy for the author to add the indexing as part of the installation process.

    I'm also interested in the results of my suggestions.

13. mikeo75
    Member
    Posted 3 years ago #

    I'm wondering if it's similar posts? I'll try to find out.

14. beisbolct
    Member
    Posted 3 years ago #

    Mikeo I was wondering if it was that too since that plugin uses a plugin library to run.

15. Dalton Rooney
    Member
    Posted 3 years ago #

    mikeo75: I am wondering if you can educate me a little bit on how you logged this query? I am interested in tuning the performance of my site and finding out where there are bottlenecks.

    In your original post, you wrote:

    Created 06:55 PM 07/28/2008
    ### 74 Queries
    ### Total time: 154, Average time: 2.08108108108108
    ### Taking 2 to 3 seconds to complete
    ### Rows analyzed 55576 - 56375

    etc., etc.

    I have shell access and am about to move to a VPS. Will I be able to do logging like this myself?

    Thanks,
    Dalton

16. mikeo75
    Member
    Posted 3 years ago #

    The plugin appears to be Similar Posts, which sucks, because that's a pretty nice feature. Not sure how to make it better or not.

    Dalton, my server at Media Temple has this in their control panel, so I'm not sure your VPS will have it.

17. mikeo75
    Member
    Posted 3 years ago #

    Actually, I think it's Taxonomy.php that is causing this problem.

    Here is what my host found:

    Unfortunately, I won't be able to pin point where this query is coming from since wordpress uses variables but you can perform searches for certain key words that might at least lower the amount of scripts. I did a quick search that looks for one of the inner joins lines

    INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id

    You can run this from within ssh

    egrep -ir "INNER JOIN .* AS tt ON t.term_id = tt.term_id" /home/43539/domains/

    This looks to be retuning files with part of this query,

    /home/43539/domains/popcritics.com/html/wp-includes/taxonomy.php
    /home/43539/domains/popcritics.com/html/wp-content/plugins/simple-tags/2.3/simple-tags.client.php
    /home/43539/domains/popcritics.com/html/wp-content/plugins/simple-tags/2.5/simple-tags.client.php
    /home/43539/domains/popcritics.com/html/wp-content/plugins/simple-tags/2.5/inc/simple-tags.admin.php

18. beisbolct
    Member
    Posted 3 years ago #

    So how do we fix the taxonomy.php file to work correctly?

19. mikeo75
    Member
    Posted 3 years ago #

    No idea, can't get an answer from anyone :(

20. beisbolct
    Member
    Posted 3 years ago #

    It sucks too because all of mysql slow queries that's causing wordpress to use too much cpu on my site is coming from it.

21. Ogre
    Member
    Posted 3 years ago #

    beisbolct, what are the queries that are causing problems?

22. beisbolct
    Member
    Posted 3 years ago #

    # Wed Aug 27 08:07:02 2008
    # Query_time: 7 Lock_time: 0 Rows_sent: 1 Rows_examined: 4
    use ianbethu_wdold;
    SELECT * , IF (DATE_ADD(link_updated, INTERVAL 120 MINUTE) >= NOW(), 1,0) as recently_updated FROM wp_links INNER JOIN wp_term_relationships AS tr ON (wp_links.link_id = tr.object_id) INNER JOIN wp_term_taxonomy as tt ON tt.term_taxonomy_id = tr.term_taxonomy_id WHERE 1=1 AND link_visible = 'Y' AND ( tt.term_id = 2 ) AND taxonomy = 'link_category' ORDER BY link_name ASC

    # Wed Aug 27 08:26:42 2008
    # Query_time: 19 Lock_time: 17 Rows_sent: 17 Rows_examined: 17
    use ianbethu_wrdp1;
    DESC wp_comments

    # Wed Aug 27 08:26:42 2008
    # Query_time: 19 Lock_time: 7 Rows_sent: 17 Rows_examined: 17
    use ianbethu_wrdp1;
    DESC wp_comments

    # Wed Aug 27 08:36:41 2008
    # Query_time: 24 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
    use ianbethu_wrdp1;
    UPDATE wp_options SET option_value = '1219847777' WHERE option_name = 'gltr_last_connection_time'

    # Wed Aug 27 09:22:20 2008
    # Query_time: 18 Lock_time: 0 Rows_sent: 16 Rows_examined: 64
    use ianbethu_wrdp1;
    SELECT * , IF (DATE_ADD(link_updated, INTERVAL 120 MINUTE) >= NOW(), 1,0) as recently_updated FROM wp_links INNER JOIN wp_term_relationships AS tr ON (wp_links.link_id = tr.object_id) INNER JOIN wp_term_taxonomy as tt ON tt.term_taxonomy_id = tr.term_taxonomy_id WHERE 1=1 AND link_visible = 'Y' AND ( tt.term_id = 88 ) AND taxonomy = 'link_category' ORDER BY link_name ASC

    # Wed Aug 27 09:22:20 2008
    # Query_time: 11 Lock_time: 0 Rows_sent: 5 Rows_examined: 25
    use ianbethu_wrdp1;
    SELECT t.*, tt.*, tr.object_id FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON tt.term_id = t.term_id INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN ('category', 'post_tag') AND tr.object_id IN (1844) ORDER BY t.name ASC

    # Wed Aug 27 09:25:27 2008
    # Query_time: 7 Lock_time: 0 Rows_sent: 4 Rows_examined: 20
    use ianbethu_wrdp1;
    SELECT t.*, tt.*, tr.object_id FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON tt.term_id = t.term_id INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN ('category', 'post_tag') AND tr.object_id IN (2629) ORDER BY t.name ASC

    # Wed Aug 27 09:25:27 2008
    # Query_time: 6 Lock_time: 0 Rows_sent: 1 Rows_examined: 4
    use ianbethu_wdold;
    SELECT * , IF (DATE_ADD(link_updated, INTERVAL 120 MINUTE) >= NOW(), 1,0) as recently_updated FROM wp_links INNER JOIN wp_term_relationships AS tr ON (wp_links.link_id = tr.object_id) INNER JOIN wp_term_taxonomy as tt ON tt.term_taxonomy_id = tr.term_taxonomy_id WHERE 1=1 AND link_visible = 'Y' AND ( tt.term_id = 2 ) AND taxonomy = 'link_category' ORDER BY link_name ASC

    # Wed Aug 27 09:25:27 2008
    # Query_time: 6 Lock_time: 0 Rows_sent: 1 Rows_examined: 4
    use ianbethu_wrdp1;
    SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) INNER JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id) WHERE 1=1 AND wp_term_taxonomy.taxonomy = 'category' AND wp_term_taxonomy.term_id IN ('858') AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 15

23. beisbolct
    Member
    Posted 3 years ago #

    ogre here are some more from today that have an absurd query time.

    # Fri Aug 29 16:03:45 2008
    # Query_time: 10 Lock_time: 6 Rows_sent: 1 Rows_examined: 0
    use ianbethu_wrdp1;
    SELECT wp_posts.* FROM wp_posts WHERE 1=1 AND wp_posts.ID = 2259 AND wp_posts.post_type = 'post' ORDER BY wp_posts.post_date DESC

    # Fri Aug 29 16:03:47 2008
    # Query_time: 6 Lock_time: 0 Rows_sent: 12 Rows_examined: 24
    use ianbethu_wrdp1;
    SELECT * FROM wp_comments WHERE comment_post_ID = 3687 AND comment_approved = '1' ORDER BY comment_date

    # Fri Aug 29 16:03:53 2008
    # Query_time: 4 Lock_time: 0 Rows_sent: 1 Rows_examined: 2882
    use ianbethu_wrdp1;
    SELECT p.* FROM wp_posts AS p WHERE p.post_date < '2007-09-17 20:00:35' AND p.post_type = 'post' AND p.post_status = 'publish' ORDER BY p.post_date DESC LIMIT 1

    # Fri Aug 29 16:13:02 2008
    # Query_time: 35 Lock_time: 0 Rows_sent: 3 Rows_examined: 15
    use ianbethu_wrdp1;
    SELECT t.*, tt.*, tr.object_id FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON tt.term_id = t.term_id INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN ('category', 'post_tag') AND tr.object_id IN (3208) ORDER BY t.name ASC

    # Fri Aug 29 16:13:02 2008
    # Query_time: 4 Lock_time: 0 Rows_sent: 2 Rows_examined: 10
    SELECT t.*, tt.*, tr.object_id FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON tt.term_id = t.term_id INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN ('category', 'post_tag') AND tr.object_id IN (3039) ORDER BY t.name ASC

    # Fri Aug 29 16:13:27 2008
    # Query_time: 16 Lock_time: 0 Rows_sent: 1 Rows_examined: 1325
    use ianbethu_wrdp1;
    SELECT p.* FROM wp_posts AS p WHERE p.post_date < '2008-04-05 17:18:33' AND p.post_type = 'post' AND p.post_status = 'publish' ORDER BY p.post_date DESC LIMIT 1

    # Fri Aug 29 16:13:42 2008
    # Query_time: 39 Lock_time: 0 Rows_sent: 1 Rows_examined: 1968
    use ianbethu_wrdp1;
    SELECT p.* FROM wp_posts AS p WHERE p.post_date < '2008-01-21 15:19:41' AND p.post_type = 'post' AND p.post_status = 'publish' ORDER BY p.post_date DESC LIMIT 1

    # Fri Aug 29 16:13:42 2008
    # Query_time: 39 Lock_time: 0 Rows_sent: 1 Rows_examined: 2137
    SELECT p.* FROM wp_posts AS p WHERE p.post_date < '2007-12-28 15:00:43' AND p.post_type = 'post' AND p.post_status = 'publish' ORDER BY p.post_date DESC LIMIT 1

    # Fri Aug 29 16:13:42 2008
    # Query_time: 29 Lock_time: 0 Rows_sent: 1 Rows_examined: 4047
    use ianbethu_wrdp1;
    SELECT p.* FROM wp_posts AS p WHERE p.post_date < '2007-04-14 18:26:11' AND p.post_type = 'post' AND p.post_status = 'publish' ORDER BY p.post_date DESC LIMIT 1

    # Fri Aug 29 16:13:43 2008
    # Query_time: 45 Lock_time: 0 Rows_sent: 15 Rows_examined: 1164
    use ianbethu_wrdp1;
    SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) INNER JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id) WHERE 1=1 AND wp_term_taxonomy.taxonomy = 'category' AND wp_term_taxonomy.term_id IN ('29') AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 15

24. beisbolct
    Member
    Posted 3 years ago #

    Here's more and I don't even use a K2 theme at all yet it's calling for a K2 in some type of query.

    # Sat Aug 30 21:24:50 2008
    # Query_time: 121 Lock_time: 78 Rows_sent: 1 Rows_examined: 1
    use ianbethu_wrdp1;
    SELECT option_value FROM wp_options WHERE option_name = 'k2installed' LIMIT 1

    # Sat Aug 30 21:24:52 2008
    # Query_time: 119 Lock_time: 74 Rows_sent: 1 Rows_examined: 1
    use ianbethu_wrdp1;
    SELECT option_value FROM wp_options WHERE option_name = 'k2installed' LIMIT 1

    # Sat Aug 30 21:24:52 2008
    # Query_time: 127 Lock_time: 81 Rows_sent: 1 Rows_examined: 1
    use ianbethu_wrdp1;
    SELECT option_value FROM wp_options WHERE option_name = 'k2installed' LIMIT 1

    # Sat Aug 30 21:24:52 2008
    # Query_time: 117 Lock_time: 72 Rows_sent: 1 Rows_examined: 1
    use ianbethu_wrdp1;
    SELECT option_value FROM wp_options WHERE option_name = 'k2installed' LIMIT 1

    # Sat Aug 30 21:24:52 2008
    # Query_time: 122 Lock_time: 77 Rows_sent: 1 Rows_examined: 1
    SELECT option_value FROM wp_options WHERE option_name = 'k2installed' LIMIT 1

    # Sat Aug 30 21:24:52 2008
    # Query_time: 115 Lock_time: 70 Rows_sent: 1 Rows_examined: 1
    use ianbethu_wrdp1;
    SELECT option_value FROM wp_options WHERE option_name = 'k2installed' LIMIT 1

    # Sat Aug 30 21:24:52 2008
    # Query_time: 120 Lock_time: 75 Rows_sent: 1 Rows_examined: 0
    use ianbethu_wrdp1;
    SELECT wp_posts.* FROM wp_posts WHERE 1=1 AND wp_posts.ID = 3836 AND wp_posts.post_type = 'post' ORDER BY wp_posts.post_date DESC

    # Sat Aug 30 21:24:53 2008
    # Query_time: 115 Lock_time: 72 Rows_sent: 1 Rows_examined: 1
    use ianbethu_wrdp1;
    SELECT option_value FROM wp_options WHERE option_name = 'k2installed' LIMIT 1

    # Sat Aug 30 21:24:53 2008
    # Query_time: 113 Lock_time: 71 Rows_sent: 1 Rows_examined: 1
    SELECT option_value FROM wp_options WHERE option_name = 'k2installed' LIMIT 1

    # Sat Aug 30 21:24:53 2008
    # Query_time: 112 Lock_time: 70 Rows_sent: 1 Rows_examined: 1
    use ianbethu_wrdp1;
    SELECT option_value FROM wp_options WHERE option_name = 'k2installed' LIMIT 1

    # Sat Aug 30 21:24:53 2008
    # Query_time: 107 Lock_time: 65 Rows_sent: 1 Rows_examined: 1
    use ianbethu_wrdp1;
    SELECT option_value FROM wp_options WHERE option_name = 'k2installed' LIMIT 1

    # Sat Aug 30 21:24:53 2008
    # Query_time: 108 Lock_time: 66 Rows_sent: 1 Rows_examined: 1
    use ianbethu_wrdp1;
    SELECT option_value FROM wp_options WHERE option_name = 'k2installed' LIMIT 1

    # Sat Aug 30 21:24:53 2008
    # Query_time: 103 Lock_time: 61 Rows_sent: 1 Rows_examined: 1
    use ianbethu_wrdp1;
    SELECT option_value FROM wp_options WHERE option_name = 'k2installed' LIMIT 1

    # Sat Aug 30 21:24:53 2008
    # Query_time: 116 Lock_time: 70 Rows_sent: 1 Rows_examined: 1
    use ianbethu_wrdp1;
    SELECT option_value FROM wp_options WHERE option_name = 'k2installed' LIMIT 1

    # Sat Aug 30 21:24:57 2008
    # Query_time: 102 Lock_time: 91 Rows_sent: 1 Rows_examined: 1
    use ianbethu_wrdp1;
    SELECT option_value FROM wp_options WHERE option_name = 'k2installed' LIMIT 1

    # Sat Aug 30 21:24:58 2008
    # Query_time: 97 Lock_time: 87 Rows_sent: 1 Rows_examined: 1
    use ianbethu_wrdp1;
    SELECT option_value FROM wp_options WHERE option_name = 'k2installed' LIMIT 1

    # Sat Aug 30 21:24:58 2008
    # Query_time: 97 Lock_time: 87 Rows_sent: 1 Rows_examined: 1
    use ianbethu_wrdp1;
    SELECT option_value FROM wp_options WHERE option_name = 'k2installed' LIMIT 1

    # Sat Aug 30 21:24:58 2008
    # Query_time: 90 Lock_time: 81 Rows_sent: 1 Rows_examined: 1
    SELECT option_value FROM wp_options WHERE option_name = 'k2installed' LIMIT 1

    # Sat Aug 30 21:24:58 2008
    # Query_time: 84 Lock_time: 75 Rows_sent: 1 Rows_examined: 1
    use ianbethu_wrdp1;
    SELECT option_value FROM wp_options WHERE option_name = 'k2installed' LIMIT 1

    # Sat Aug 30 21:24:58 2008
    # Query_time: 82 Lock_time: 73 Rows_sent: 1 Rows_examined: 1
    use ianbethu_wrdp1;
    SELECT option_value FROM wp_options WHERE option_name = 'k2installed' LIMIT 1

    # Sat Aug 30 21:24:58 2008
    # Query_time: 81 Lock_time: 72 Rows_sent: 1 Rows_examined: 1
    use ianbethu_wrdp1;
    SELECT option_value FROM wp_options WHERE option_name = 'k2installed' LIMIT 1

    # Sat Aug 30 21:24:58 2008
    # Query_time: 67 Lock_time: 59 Rows_sent: 1 Rows_examined: 1
    use ianbethu_wrdp1;
    SELECT option_value FROM wp_options WHERE option_name = 'k2installed' LIMIT 1

    # Sat Aug 30 21:24:58 2008
    # Query_time: 72 Lock_time: 64 Rows_sent: 1 Rows_examined: 1
    use ianbethu_wrdp1;
    SELECT option_value FROM wp_options WHERE option_name = 'k2installed' LIMIT 1

    # Sat Aug 30 21:24:58 2008
    # Query_time: 41 Lock_time: 36 Rows_sent: 1 Rows_examined: 1
    use ianbethu_wrdp1;
    SELECT option_value FROM wp_options WHERE option_name = 'k2deliciousname' LIMIT 1

25. Ogre
    Member
    Posted 3 years ago #

    > Query_time: 121 Lock_time: 78 Rows_sent: 1 Rows_examined: 1

    Is that in seconds? There is no reason a query with so few rows involved to take that long. There is something wrong with the mysql server that your host needs to address.

    Ok, there's one possibility that may not be their fault. If something keeps locking tables or the entire database, it will block the query. I see there are some pretty high lock times. That's a very bad thing. Remove whatever is doing the locks

26. beisbolct
    Member
    Posted 3 years ago #

    I fixed that problem, now i'm getting lock times from a different part of the database that queries the comments.

27. Ogre
    Member
    Posted 3 years ago #

    There is never an reason to lock tables on a simple select query. Something must be doing inserts or updates. Perhaps a logging module that is recording how often a query is viewed?

28. beisbolct
    Member
    Posted 3 years ago #

    I've moved to a new host and no problems so far. It could have been my previous hosts mysql. I have no idea. I don't know mysql that well other than basic things.

29. Niteblade
    Member
    Posted 3 years ago #

    The number one query eating my sever alive is:

    SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) INNER JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id) WHERE 1=1 AND wp_term_taxonomy.taxonomy = 'category' AND wp_term_taxonomy.term_id IN ('11') AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 1;

    Running Explain:

    id = 1
    select_type = simple
    type = const
    possible_keys = PRIMARY,term_id_taxonomy,term_taxonomy_id
    key = term_id_taxonomy
    key_len = 106
    ref = const,const
    rows = 1
    extra = Using index; Using temporary; Using filesort

    id = 1
    select_type = simple
    type = index
    possible_keys = primary
    key = primary
    key_len = 16
    ref = null
    rows = 117780
    extra = Using where; Using index

    id = 1
    select_type = simple
    type = ref
    possible_keys = primary
    key = primary
    key_len = 132
    ref = shop_shopus.wp_term_relationships.object_id,const,...
    rows = 1
    extra = Using where

    Massive amounts CPU power is used, and the dedicated server becomes sluggish for a few minutes until this query has passed ... like a kidney stone.

30. beisbolct
    Member
    Posted 3 years ago #

    That's exactly the same problem i'm having again.

Topic Closed

This topic has been closed to new replies.