0

I'm trying to optimize a query which uses a combination of GROUP BY and ORDER BY which together cause a big performance hit (~x15). If I remove either of the conditions, the query runs pretty fast. I tried different indexing strategies and query structures, but have come up empty so far.

The original query looks like this:

SELECT `academies_calendar`.`day`, `academies_calendar`.`start`, `academies_calendar`.`duration`,
 `academies_calendar`.`title`, `academies_calendar`.`id`, TIME_TO_SEC(TIMEDIFF('00:16:00', academies_calendar.start)) AS `diff`, 
 IF('00:16' BETWEEN academies_calendar.start - INTERVAL 60 MINUTE AND academies_calendar.start + INTERVAL (academies_calendar.duration + 60) MINUTE,1,0) AS `active`, 
 IF('00:16' BETWEEN academies_calendar.start - INTERVAL 60 MINUTE AND academies_calendar.start + INTERVAL (academies_calendar.duration) MINUTE,1,0) AS `starting`, 
 `academies_calendar`.`instructor_id`, CONCAT_WS(' ',academies_instructors.first_name,academies_instructors.last_name) AS `instructor`,
 COUNT(members_attendance.id) AS `attending`, GROUP_CONCAT( CONCAT_WS('>|<',members.first_name,members.last_name,members.academy_id,members.photo) SEPARATOR '<^>') AS `members` 
FROM `academies_calendar` 
LEFT JOIN `academies_instructors` ON academies_instructors.id=academies_calendar.instructor_id 
LEFT JOIN `members_attendance` ON members_attendance.event_id=academies_calendar.id AND (DATE(members_attendance.checked_in)=CURDATE()) 
LEFT JOIN `members` ON members.id=members_attendance.member_id 
WHERE (academies_calendar.academy_id=3) 
AND academies_calendar.day=2 
AND academies_calendar.start > CAST('00:16' as time) - INTERVAL (academies_calendar.duration + 60) MINUTE
GROUP BY `academies_calendar`.`id` 
ORDER BY ISNULL(academies_calendar.start),academies_calendar.start ASC

It's pulling a bit of data and organizing it, but due to the filters that hit indexes, it's usually working on small set of data - 1-6 entries in the academies_calendar table.

The EXPLAIN results for that query:

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra   
1   SIMPLE  academies_calendar  ref     PRIMARY,academy_id  academy_id  6   const,const     5   Using where; Using temporary; Using filesort
1   SIMPLE  academies_instructors   eq_ref  PRIMARY     PRIMARY     4   maonrails.academies_calendar.instructor_id  1   NULL
1   SIMPLE  members_attendance  ref     event_id    event_id    5   maonrails.academies_calendar.id     5   Using where
1   SIMPLE  members     eq_ref  PRIMARY     PRIMARY     4   maonrails.members_attendance.member_id  1   Using where

The key used for the main table (academy_id) is a composite key over (academy_id,day,start). The rest are hitting indexes or primary keys. Only 5 rows are found for this specific query, and it complete in 0.015s

If I remove either the ORDER BY clause, or the GROUP_CONCAT() call, the query runs at around 0.0009s.

I'm not sure I understand this behavior - isn't the ORDER BY clause supposed to be applied after the WHERE clauses and GROUP clauses have been applied? why does sorting 5 rows increase query time by more than an order of magnitude? I get the same results if the query returns just 1 result, by the way.

What seems to be happening, is that the grouping multiplies the amount of time it needs to sort the result set by the amount of rows joined to it from the other tables. I tried to resolve that by replacing those joins with a subquery that groups the data and returns one result per row, but it did not have a significant impact on query run time.

Any ideas on how to optimize this query would be much appreciated!

  • 1
    Hmmm... there could be caching going on which is reducing your apparent "disk-read" time - in fact, the data is coming straight from an in-memory buffer - and all the work is in the sort - for which 15 milliseconds doesn't seem unreasonable. Maybe if you tried performing your queries after a reboot of the mysqld daemon, you might see a better comparison. I've rarely heard people complain about a 15 millisecond response time :-) – Vérace Jul 5 '16 at 7:56
  • He could also use SQL_NO_CACHE in the SELECT dev.mysql.com/doc/refman/5.7/en/query-cache-in-select.html – Craig Efrein Jul 5 '16 at 7:58
  • Those queries are run on a dev machine with the SQL cache turned off. For added assurance, I ran it again with SQL_NO_CACHE with same results – Eran Galperin Jul 5 '16 at 13:23
  • The row counts are good; there are very few disk hits, even if cache is cold. I don't consider 15ms for a query this complex to be worth optimizing. – Rick James Jul 11 '16 at 1:13
  • If checked_in is a DATE, don't hide it in a function. – Rick James Jul 11 '16 at 1:14
1
  • Remove LEFT if you don't need it.
  • The ORDER BY has ISNULL(start), yet the WHERE clause will fail if start isNULL`. Seems like something could be simplified.
  • Will the table(s) become so big that they cannot be cached in RAM? If so, we can talk about the choice of PRIMARY KEY. (I'll need to see SHOW CREATE TABLE for this discussion.)
  • The query, as it stands, is destined to take some time...

It needs to look at all index rows with academy_id=3 and day=2, then filter on start. That is currently about 5 rows; will that increase when there is more data? Will you end up with a LIMIT?

Will the test on start be a significant factor in the filtering? If so we can discuss a 'lazy eval' technique.

Here's one thing that may help: Remove the LEFT JOIN members and replace the GROUP_CONCAT... with:

( SELECT GROUP_CONCAT(...) FROM members AS m
      WHERE m.id = members_attendance.member_id ) AS members,

Also, change

(DATE(members_attendance.checked_in)=CURDATE())

to

members_attendance.checked_in >= CURDATE() AND
members_attendance.checked_in  < CURDATE() + INTERVAL 1 DAY

and add

INDEX(event_id, checked_in)  -- in that order

(unless that table has PRIMARY KEY(event_id) -- it would help if you showed us SHOW CREATE TABLE)

Explanation

Some of the question deals with the slowdown due to ORDER BY. Let me walk you through some of the steps:

  1. fetch things from the first table, filtering based on the WHERE
  2. fetch things from JOINed tables, again filtering where appropriate
  3. GROUP BY id -- Since the joins created an explosion of rows in a tmp table, but you wanted only one row per id, this shrinks things back down. It may require a temp table and a sort. (Note: My suggestion on GROUP_CONCAT avoids some of the explosion.)
  4. ORDER BY something_else -- If the ORDER BY had been on the same value(s), it could be subsumed into the GROUP BY. But, since it is not, there needs to be another temp table and sort.

Caveat: "temp table" may or may not be entirely in RAM. "sort" may or may not be in RAM. If either of these hits disk, the query will be slower.

Caveat: If any of the SELECTed columns are TEXT or BLOB, the temp table will be headed for disk. (The whole story is more complex; let's see SHOW CREATE TABLE.)

It may be possible to get rid of all JOINs, such as my suggestion above. If so, then you can get rid of the GROUP BY, thereby speeding things up.

Do what you can in the directions I suggest, then let's make another pass at it. (There may need to be further index changes.)

<my-rant>
    3rd party software tries to help with DB abstraction,
    but that usually gets in the way of performance.
</my-rant>
  • I added some more, in an attempt to explain why ORDER BY slows things down in your case. – Rick James Jul 11 '16 at 22:29
  • Thanks for taking the time to write this up. To answer your questions: * The ISNULL() part of the order clause is there to prevent NULL results from appearing first. Removing it does not affect execution time * 2-6 rows are expected to the dataset after academy_id and day filters are applied. It would be rare for the result to be larger, and a limit would never be applied. * The Table will likely fit in RAM for the foreseable future * Regarding removing the LEFT JOIN - I mentioned in my original question that I replaced it with a subquery, and it didn't improve matters – Eran Galperin Jul 12 '16 at 4:29

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.