Search code examples
mysqloptimizationquery-optimization

About optimising MySQL queries: Simple MySQL query takes more than 3 seconds

To my knowledge this following query is not too complicated, but it still takes more than 3 seconds. Any ideas on how to optimise this?

SELECT
    wpp.ID, wpp.post_title, wpp.post_author,
    wpp.post_status, s.supplier_company
FROM wp_posts AS wpp 
LEFT JOIN wp_postmeta AS postmeta ON wpp.ID = postmeta.post_id 
LEFT JOIN wp_term_relationships AS term_link ON wpp.ID = term_link.object_id 
LEFT JOIN wp_terms AS terms ON term_link.term_taxonomy_id = terms.term_id 
LEFT JOIN wp_teleapo_supplier AS s ON wpp.post_author = s.ID 
WHERE wpp.post_type = 'post'
AND wpp.post_warning <> 'no_image'
AND wpp.post_status <> 'trash'
AND wpp.post_status <> 'auto-draft'
GROUP BY wpp.ID 
ORDER BY post_date DESC 
LIMIT 100 OFFSET 0

All my tables have around 2000~9000 posts now.
1) 15000 records in wp_posts but only around 3000 with WHERE wpp.post_type = 'post'.
2) around 9000 in the wp_term_relationships
But are prone to grow in the near future...

The Origin of the problem:

In the short query above, if I change:

GROUP BY wpp.ID 
ORDER BY post_date DESC

TO

GROUP BY wpp.ID 
ORDER BY wpp.ID DESC

My query time goes from 3.3 sec to 0.3 sec... However I'd like a way so I can still ORDER BY post_date!

Something that's even weirder is that the full search query down below goes at under 1 sec even when the GROUP and ORDER BY's are different...

EXPLAIN results for the short query:

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  wpp ref type_status_date,post_type,post_status  post_type   62  const   3351    Using where; Using temporary; Using filesort
1   SIMPLE  postmeta    ref post_id post_id 8   r_11524_jtc.wpp.ID  7   Using index
1   SIMPLE  term_link   ref PRIMARY PRIMARY 8   r_11524_jtc.wpp.ID  92  Using index
1   SIMPLE  terms   eq_ref  PRIMARY PRIMARY 8   r_11524_jtc.term_link.term_taxonomy_id  1   Using index
1   SIMPLE  s   eq_ref  ID  ID  4   r_11524_jtc.wpp.post_author 1

These are the INDEXES I have:

enter image description here

The full search depending on the user wether or not he fills in some search fields is down below. The weirdest thing is that the search query below takes only 0.3 sec even if the ORDER BY column is different from the GROUP BY column!

SELECT  SQL_CALC_FOUND_ROWS wpp.ID, wpp.post_title, wpp.post_author,
        wpp.post_status, s.supplier_company,
        GROUP_CONCAT(DISTINCT terms.slug SEPARATOR ',') AS allslug,
        GROUP_CONCAT(DISTINCT terms.name SEPARATOR ',') AS allcatname
    FROM  wp_posts AS wpp
    LEFT JOIN  wp_postmeta AS postmeta ON wpp.ID = postmeta.post_id
    LEFT JOIN  wp_term_relationships AS term_link ON wpp.ID = term_link.object_id
    LEFT JOIN  wp_terms AS terms ON term_link.term_taxonomy_id = terms.term_id
    LEFT JOIN  wp_teleapo_supplier AS s ON wpp.post_author = s.ID
    WHERE  wpp.post_type = 'post'
      AND  wpp.post_warning <> 'no_image'
      AND  wpp.post_status <> 'trash'
      AND  wpp.post_status <> 'auto-draft' /* All search on post_title
      and  any postmeta value */
      AND  (post_title LIKE '%textile%'
        OR  postmeta.meta_value LIKE '%textile%')
              /* extra filters. The one below is an example of a filter on
                 the user #324 but this field can also take the username
                 (supplier_company) parameters etc. */
      AND  ( wpp.post_author LIKE '%324%'
              OR  ( EXISTS 
                      ( SELECT  1
                            FROM  wp_teleapo_supplier as s2
                            WHERE  s2.ID = wpp.post_author
                              AND  (s2.supplier_company      LIKE '%324%'
                                OR  s2.supplier_company_kana LIKE '%324%') ) )
              OR  ( EXISTS 
                      ( SELECT  1
                            FROM  wp_postmeta AS postmeta2
                            WHERE  postmeta2.post_id = wpp.ID
                              AND  postmeta2.meta_key = 'input_comp'
                              AND  postmeta2.meta_value LIKE '%324%' ) ) )
    GROUP BY  wpp.ID /* Filter on Categories!! */
    HAVING  ( allcatname LIKE '%apparel-and-accessories%'
             OR  allslug LIKE '%apparel-and-accessories%' )
    ORDER BY  post_date DESC
    LIMIT  20 OFFSET 0
Solution

  • Idea A

    Potential problem: Without the GROUP BY, do you ever see multiple supplier_company values for one wpp.ID? If so, which one do you want? Or do you want GROUP_CONCAT(s.supplier_company)?

    Idea B

    Do you want NULL to come out for supplier_company? If so, you may be stuck with this slow query. (And blame EAV schema design that WP encourages.)

    If you would rather skip the NULLs, then get rid of all the LEFTs. With the LEFT, the query is gathering all sorts of NULLs, only to eventually toss them.

    Idea C

    Also, without the LEFTs, it may be possible to walk through the tables in the opposite order. That is, starting with wp_teleapo_supplier and seeing which posts pop up. (There may need to be some more indexes in order to follow the tables in that order.)

    Idea D

    If the GROUP BY is not needed, remove it and replace INDEX(post_type) with INDEX(post_type, post_date). That way, it might be able to use the index to stop when only 100 rows have been found. Or is post_date not in wpp??? Please qualify all columns when JOINing.

    Other

    Some of the comments imply you left out some info. In that case, my advice could be useless. Please do not 'simplify' the query.

    s has no PRIMARY KEY? That is a no-no. Please provide SHOW CREATE TABLE wp_teleapo_supplier so we can discuss what the PK should be.

    Longer Query

    A number of performance killers show up in the longer query

    • LIKE '%...' -- cannot use index
    • OR -- cannot use index. One workaround is to reformulate the query into a UNION, but that would be too much of a nightmare, considering the multiple ORs.
    • GROUP BY and ORDER BY on different column(s). Even if they were on the same column(s) there is too much going on in this query to get much benefit.
    • OFFSET smells like "pagination".
    • SQL_CALC_FOUND_ROWS -- the query has to finish out finding everything. Since the LIMIT cannot be done with an index, it does not matter.

    The only hope I can think of is to redesign the schema and the UI with search in mind. There can be no significant speedup for that query.