MySQL Slow query problem

  • 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;

Viewing 15 replies - 1 through 15 (of 31 total)
  • Thread Starter mikeo75

    (@mikeo75)

    Anyone?

    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.

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

    Thread Starter mikeo75

    (@mikeo75)

    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 ??

    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.

    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.

    how do you do the index…

    index wp_taxonomy.taxonomy

    i am having the same problem on mine as well.

    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’ );

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

    Thread Starter mikeo75

    (@mikeo75)

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

    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.

    Thread Starter mikeo75

    (@mikeo75)

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

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

    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

    Thread Starter mikeo75

    (@mikeo75)

    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.

Viewing 15 replies - 1 through 15 (of 31 total)
  • The topic ‘MySQL Slow query problem’ is closed to new replies.