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 OR
ed 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)
Assuming that you range partition your table by the (auto incrementing) sequence and you limit your query by another value (day number), you might see, that a lot of partitions will be touched when you use:
EXPLAIN PARTITIONS SELECT * FROM table_name WHERE day_number=X ORDER BY...
The reason for that is, that there is no linkage between the partitioning attribute and the data filter. Since MySQL uses a different index for each partition, it needs to open all partitions to find all data matching day_number.
If you know the partition name, maybe by querying INFORMATION_SCHEMA.PARTITIONS before the real select, and you use MySQL-5.6+, you can use partition selection like
SELECT * FROM table_name PARTITION(partition_name) WHERE day_number=X ORDER BY...
But this is just chitchat based on a lot of assumtions. To give you a real answer, you need to post the query plan and more information about table structure - like Phil said before.
Best Answer
All you need to do is
ORDER BY startDate ASC
first, thenfeatured DESC
:Note: I've commented the
categories
table because you didn't specified which is its structure.Test:
Test it in this Fiddle.