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

Question

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:

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

Dear @PM77-1 Thanks!! I updated the question with my indexes and table sizes. — mesqueeb, Mar 28 '16 at 04:47
What happens when you run that query thru [Explain](http://dev.mysql.com/doc/refman/5.7/en/explain.html)? — Drew, Mar 28 '16 at 04:49
@Drew I added the EXPLAIN results, but it's difficult to read... — mesqueeb, Mar 28 '16 at 04:52
I realize it says possible_keys, and then chose a key. But is does say "... Using temporary; Using filesort" . So I think you might have gotten bit by [this answer](http://stackoverflow.com/a/13639502) due to the group by and order by. — Drew, Mar 28 '16 at 05:20
The first and last rows don't say "Using index". Is that bad? — mesqueeb, Mar 28 '16 at 05:24
First row, yes. Last row, no. Because using an index on few rows takes longer. I also think you have more data than you are letting on to having, based on the cardinality numbers. — Drew, Mar 28 '16 at 05:25
Hey @Drew Do you know which index I'm missing in the first one? And btw, I have indeed around 15,000 records in `wp_posts` but only around `3000` with this clause: `WHERE wpp.post_type = 'post'` Sorry for explaining it wrongly. — mesqueeb, Mar 28 '16 at 05:31
wp_posts.post_type has low cardinality. That is not helping much. — Drew, Mar 28 '16 at 05:36
@Drew because i need it for group_concats that i omitted here. — mesqueeb, Mar 28 '16 at 05:38
too funny. Also, if you are using aliases (a good idea), use them everywhere. ie: ORDER BY post_date DESC ... because if that column is in a few places not that it is (or you would get ambiguity errors). All it does is slow down the engine to figure it out. I will try to go hunt down 2 guys I know. — Drew, Mar 28 '16 at 05:40
@Drew when deleting `ORDER BY post_date DESC` the temporarily table shown in the first line of EXPLAIN isn't used anymore and the query only takes 1.5 seconds. But i kind of need the `ORDER BY`. And what do you mean by hunting down two people you know? Lol — mesqueeb, Mar 28 '16 at 06:05
asked Tim to pop on by. Maybe another guy when he comes on line if Tim can't help. But I think that link above sheds some light on it. Also, as you said, you may not be showing the entire query (group concat) — Drew, Mar 28 '16 at 06:06
@Drew i omitted group_concat because it takes 3 sec even without group_concat. When the user does not fill in some search input box, the group_concat is not pasted in the sql query. — mesqueeb, Mar 28 '16 at 06:09

Rick James · Accepted Answer · 2016-03-29T03:46:14.513

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.

Good morning Rick! **A)** The posts always have 0 or 1 record linked in the suppliers table, so no need to group_concat there. **B)** When there's no supplier data, it's ok to get NULL. **C)** The search function retrieves posts and the mysql query becomes larger as the user searches for certain things. e.g. filter posts on supplier / filter on category, etc. that's why I think I need to start from the `wp_posts` table. The reason for my GROUP BY is because I need to be able to filter on category. — mesqueeb, Mar 29 '16 at 00:49
As long as I cannot solve a better way to filter on Categories, I need the `GROUP_CONCAT` + `HAVING` + `GROUP BY` way of filtering my posts. My problem is discribed here: http://stackoverflow.com/questions/36246152/using-mysql-exists-with-double-left-join-using-a-join-table — mesqueeb, Mar 29 '16 at 00:50
`wp_teleapo_supplier` had indeed no Primary Key. I added it to the `ID` column now! — mesqueeb, Mar 29 '16 at 00:56
Dear Rick, the problem lies is GROUP BY and ORDER BY being a different column. I updated my question a bit, could you have another look? — mesqueeb, Mar 29 '16 at 01:04
The other question you linked to seems quit different, and probably can be solved fairly cleanly. I supplied an answer. I don't see how it relates to this question. — Rick James, Mar 29 '16 at 03:57

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

1 Answers1