Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table that stores available appointments for teachers, allowing two kinds of insertions:

  1. Hourly based: with total freedom to add unlimited slots per day per teacher (as long as slots don't overlap): on 15/Apr a teacher may have slots at 10:00, 11:00, 12:00 and 16:00. A person is served after choosing a specific teacher time/slot.

  2. Time period/range: on 15/Apr another teacher may work from 10:00 to 12:00 and then from 14:00 to 18:00. A person is served by order of arrival, so if a teacher works from 10:00 to 12:00, all persons that arrive in this period will be attended by order of arrival (local queue).

Since I have to return all available teachers in a search, I need all slots to be saved in the same table as the order of arrival ranges. This way I can order by date_from ASC, showing the first available slots first on the search results.

Current table structure

CREATE TABLE `teacher_slots` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `teacher_id` mediumint(8) unsigned NOT NULL,
  `city_id` smallint(5) unsigned NOT NULL,
  `subject_id` smallint(5) unsigned NOT NULL,
  `date_from` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `date_to` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `status` tinyint(4) NOT NULL DEFAULT '0',
  `order_of_arrival` tinyint(1) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `by_hour_idx` (`teacher_id`,`order_of_arrival`,`status`,`city_id`,`subject_id`,`date_from`),
  KEY `order_arrival_idx` (`order_of_arrival`,`status`,`city_id`,`subject_id`,`date_from`,`date_to`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Search query

I need to filter by: actual datetime, city_id, subject_id and if a slot is available (status = 0).

For hourly based I have to show all available slots for the first closest available day for every teacher (show all time slots of a given day and can't show more than one day for the same teacher). (I got the query with the help from mattedgod).

For range based (order_of_arrival = 1), I have to show the closest available range, just one time per teacher.

First query runs individually in around 0.10 ms, second query 0.08 ms and the UNION ALL an average of 300ms.

(
    SELECT id, teacher_slots.teacher_id, date_from, date_to, order_of_arrival
    FROM teacher_slots
    JOIN (
        SELECT DATE(MIN(date_from)) as closestDay, teacher_id
        FROM teacher_slots
        WHERE   date_from >= '2014-04-10 08:00:00' AND order_of_arrival = 0
                AND status = 0 AND city_id = 6015 AND subject_id = 1
        GROUP BY teacher_id
    ) a ON a.teacher_id = teacher_slots.teacher_id
    AND DATE(teacher_slots.date_from) = closestDay
    WHERE teacher_slots.date_from >= '2014-04-10 08:00:00'
        AND teacher_slots.order_of_arrival = 0
        AND teacher_slots.status = 0
        AND teacher_slots.city_id = 6015
        AND teacher_slots.subject_id = 1
)

UNION ALL

(
    SELECT id, teacher_id, date_from, date_to, order_of_arrival
    FROM teacher_slots
    WHERE order_of_arrival = 1 AND status = 0 AND city_id = 6015 AND subject_id = 1
        AND (
            (date_from <= '2014-04-10 08:00:00' AND  date_to >= '2014-04-10 08:00:00')
            OR (date_from >= '2014-04-10 08:00:00')
        )
    GROUP BY teacher_id
)

ORDER BY date_from ASC;

Question

Is there a way to optimise the UNION, so I can get a reasonable response of a maximum ~20ms or even return range based + hourly based in just one query (with an IF, etc)?

SQL Fiddle: http://www.sqlfiddle.com/#!2/59420/1/0

EDIT:

I tried some denormalization by creating a field "only_date_from" where I stored only the date, so I could change this...

DATE(MIN(date_from)) as closestDay / DATE(teacher_slots.date_from) = closestDay

... to this

MIN(only_date_from) as closestDay / teacher_slots.only_date_from = closestDay

It already saved me 100ms! Still 200ms on average.

share|improve this question

migrated from stackoverflow.com Apr 11 '13 at 17:03

This question came from our site for professional and enthusiast programmers.

    
My suspicion is that the ORDER BY clause is causing the time to be much more than the individual queries. Not sure off the top of my head how to solve though. –  Andy Nichols Apr 11 '13 at 14:17
1  
How does the UNION perform without the ORDER BY? –  Alkini Apr 11 '13 at 14:17
    
I suspect that what's really bogging this down is the ORDER BY after your UNION. Is that something that you could do on the frontend? –  Jeff Rosenberg Apr 11 '13 at 14:17
1  
How many rows returned for each query individually? I'm wondering if the union of the two sets is exceeding some threshold (eg RAM?) that isn't reached by either query in isolation. edit: Does artificially reducing the size of one or both queries (eg with a limit) make the union time more proportional? –  Sepster Apr 11 '13 at 14:20
1  
Alfred, it might be better to bring this conversation to The Heap instead of continuing it here? The comment threads here are starting to look a little unwieldy :) –  Jack Douglas Apr 12 '13 at 8:36
show 18 more comments

2 Answers

Try this query:

(
select * from (SELECT id, teacher_slots.teacher_id, date_from, date_to,  order_of_arrival
FROM teacher_slots  WHERE teacher_slots.date_from >= '2014-04-10 08:00:00'
    AND teacher_slots.order_of_arrival = 0
    AND teacher_slots.status = 0
    AND teacher_slots.city_id = 6015
    AND teacher_slots.subject_id = 1) 
 teacher_slots
JOIN (
    SELECT DATE(MIN(date_from)) as closestDay, teacher_id
    FROM teacher_slots
    WHERE   date_from >= '2014-04-10 08:00:00' AND order_of_arrival = 0
            AND status = 0 AND city_id = 6015 AND subject_id = 1
    GROUP BY teacher_id
) a ON a.teacher_id = teacher_slots.teacher_id
AND DATE(teacher_slots.date_from) = closestDay

)

UNION ALL

(
SELECT id, teacher_id, date_from, date_to, order_of_arrival
FROM teacher_slots
WHERE order_of_arrival = 1 AND status = 0 AND city_id = 6015 AND subject_id = 1
    AND (
        (date_from <= '2014-04-10 08:00:00' AND  date_to >= '2014-04-10 08:00:00')
        OR (date_from >= '2014-04-10 08:00:00')
    )
GROUP BY teacher_id
)

ORDER BY date_from ASC;
share|improve this answer
    
Actually I got the same results. –  AlfredBaudisch Apr 11 '13 at 14:43
    
Mmm....i think that reducing the universe of the query before the join would reduce the query time. –  Robert Rozas Apr 11 '13 at 14:45
    
And this query take less execution time than the original on the fiddle....it's strange. –  Robert Rozas Apr 11 '13 at 14:51
    
that's curious, because now I made another test and it took 1.62s! –  AlfredBaudisch Apr 11 '13 at 14:56
    
I just reduce the first universe...let me explain....if you both tables have 1 million records...and joining them take alot of time and you loose perf....but if you reduce in this case this first universe and made the filters there(now only have 200k of regs) the join take less time.... –  Robert Rozas Apr 11 '13 at 15:02
add comment

Firstly, I think your original query may not be "correct"; With reference to your SQLFiddle, it looks to me as though you should be returning rows with ID = 2, 3 and 4 (in addition to the row with ID = 1 you are getting from this half), because your existing logic appears as though you intended for these other rows to be included, as they explicitly meet the OR (date_from >= '2014-04-10 08:00:00') part of your second WHERE clause.

The GROUP BY teacher_id clause in your second part of your UNION is causing you to lose those rows. This is because you're not actually aggregating any columns in your select list, and in this case the GROUP BY will cause 'difficult to define' behaviour.

Also, while I can't explain the poor performance of your UNION, I can work around it for you by outright removing it from your query:

Rather than using two separate (and in parts, repeating) sets of logic to get rows from the same table, I've consolidated your logic into one query with the differences in your logic ORed together - ie if a row meets one or the other of your original WHERE clauses, it's included. This is possible because I've replaced the (INNER) JOIN you were using to find the closestDate with a LEFT JOIN.

This LEFT JOIN means we are now also able to distinguish which set of logic should be applied to a row; If the join works (closestDate IS NOT NULL) we apply your logic from the first half, but if the join fails (closestDate IS NULL) then we apply the logic from your second half.

So this will return all the rows that your query returned (in the fiddle), and it's also picking up those additional ones.

  SELECT
    *

  FROM 
    teacher_slots ts

    LEFT JOIN 
    (
      SELECT 
        teacher_id,
        DATE(MIN(date_from)) as closestDay

      FROM 
        teacher_slots

      WHERE   
        date_from >= '2014-04-10 08:00:00' 
        AND order_of_arrival = 0
        AND status = 0 
        AND city_id = 6015 
        AND subject_id = 1

      GROUP BY 
        teacher_id

    ) a
    ON a.teacher_id = ts.teacher_id
    AND a.closestDay = DATE(ts.date_from)

  WHERE 
    /* conditions that were common to both halves of the union */
    ts.status = 0
    AND ts.city_id = 6015
    AND ts.subject_id = 1

    AND
    (
      (
        /* conditions that were from above the union 
           (ie when we joined to get closest future date) */
        a.teacher_id IS NOT NULL
        AND ts.date_from >= '2014-04-10 08:00:00'
        AND ts.order_of_arrival = 0
      ) 
      OR
      (
        /* conditions that were below the union 
          (ie when we didn't join) */
        a.teacher_id IS NULL       
        AND ts.order_of_arrival = 1 
        AND 
        (
          (
            date_from <= '2014-04-10 08:00:00' 
            AND  
            date_to >= '2014-04-10 08:00:00'
          )

          /* rows that met this condition were being discarded 
             as a result of 'difficult to define' GROUP BY behaviour. */
          OR date_from >= '2014-04-10 08:00:00' 
        )
      )
    )

  ORDER BY 
   ts.date_from ASC;

Further, you can "tidy up" your query further so that you don't need to "plug in" your status, city_id and subject_id parameters more than once.

To do this, change the subquery a to also select those columns, and to also group on those columns. Then, the JOIN's ON clause would need to map those columns to their ts.xxx equivalents.

I don't think this will negatively effect performance, but couldn't be sure without testing on a large dataset.

So your join will look more like:

LEFT JOIN 
(
  SELECT 
    teacher_id,
    status,
    city_id,
    subject_id,
    DATE(MIN(date_from)) as closestDay

  FROM 
    teacher_slots

  WHERE   
    date_from >= '2014-04-10 08:00:00' 
    AND order_of_arrival = 0
  /* These no longer required here...
    AND status = 0 
    AND city_id = 6015 
    AND subject_id = 1
  */

  GROUP BY 
    teacher_id,
    status,
    city_id,
    subject_id

) a
ON a.teacher_id = ts.teacher_id
AND a.status = ts.status 
AND a.city_id = ts.city_id 
AND a.subject_id = ts.city_id
AND a.closestDay = DATE(ts.date_from)
share|improve this answer
    
Thanks for the careful analysis! Unfortunately the query doesn't return the desired logic and results: sqlfiddle.com/#!2/59420/62/0 - The expected return should be exactly as the return set of my 1st Fiddle: sqlfiddle.com/#!2/59420/1/0 - I'll take a better look at your query now and see what I can fix to get the desired return. –  AlfredBaudisch Apr 12 '13 at 11:21
    
Unfortunately no matter what I try, your query never return the expected. As I said my fiddle returns the exact logic (try setting status = 1 in some rows in my fiddle and see that when by order, it returns the next range, and when by hours it correctly shows only the available hours of the closest day). To make things worse in my final data, I had to add an ORDER BY date_from ASC to the join. It's not 200-300ms, it is now 400ms. But after hours of tests, now results are perfect. I think I'll try a mix of memcached + cron jobs. And everytime a slot is taken I regenerate the related cache. –  AlfredBaudisch Apr 12 '13 at 11:57
    
@AlfredBaudisch Ok, so we do need to exclude those extra rows, understood. I'm not clear on if your comments relating to ORDER BY date_from ASC and the jump to 400ms was related to my version of the query or yours... So do you want me to persist with making my version return the correct rows (ie did my version perform well)? I don't think it'll be hard to join back on itself to get the single best row for when order_of_arrival=1... which would be an explicit approach to getting it, rather than relying on GROUP BY to continue automagically returning the right one. –  Sepster Apr 12 '13 at 14:34
    
Actually the ORDER BY was added to my join, it was needed. I just went from 400ms to 90ms (in which I can live with!), by simplifying the 1st query where condition and by removing the order_arrival_idx and adding date_to at the end of the by_hour_idx. Since looks like SQLFiddle died, here is the new structure and new query: gist.github.com/alfredbaudisch/81c79db5f2d4053f1707 - Thanks for your help Sepster! –  AlfredBaudisch Apr 12 '13 at 15:57
    
@AlfredBaudisch so do you agree that my query is returning the rows you need, but with extra rows that you don't want when order_of_arrival=1? And if so, how quickly does it run? –  Sepster Apr 12 '13 at 16:17
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.