Dunno what else to do about this one. I have one table that has has a start and a stop columns and I want to return the results of it joined both by start and by stop and I want a clear distinction between the two.
Now both queries run fast separatly:
SELECT
UNIX_TIMESTAMP(CONVERT_TZ(start_dev, '+00:00', GetCarrierTimezone(a0.carrier_id))) AS alertStart,
NULL AS alertStop,
c0.name AS carrier_name,
carrier_image,
l0.Latitude,
l0.Longitude
FROM
carriers AS c0
INNER JOIN start_stop AS a0 ON a0.carrier_id = c0.id
INNER JOIN pcoarg AS l0 ON a0.startLogId = l0.id
WHERE
FIND_IN_SET(a0.carrier_id, '89467,1,64578,222625,45013') > 0
AND
start_dev > '2013-03-11 11:46:48'
AND
start_dev = (SELECT MIN(start_dev) FROM start_stop AS a1 WHERE a0.carrier_id = a1.carrier_id AND DATE(a1.start_dev) = DATE(a0.start_dev))
AND IsNotificationInSchedule(22, start_dev) > 0
So this one takes 0.063.
But if I combine it in a UNION (doesn't matter if it's UNION ALL OR DISTINCT OR WHATEVER) it just takes about 0.400 seconds.
SELECT * FROM
(
(
SELECT
UNIX_TIMESTAMP(CONVERT_TZ(start_dev, '+00:00', GetCarrierTimezone(a0.carrier_id))) AS alertStart,
NULL AS alertStop,
c0.name AS carrier_name,
carrier_image,
l0.Latitude,
l0.Longitude
FROM
carriers AS c0
INNER JOIN start_stop AS a0 ON a0.carrier_id = c0.id
INNER JOIN pcoarg AS l0 ON a0.startLogId = l0.id
WHERE
FIND_IN_SET(a0.carrier_id, '89467,1,64578,222625,45013') > 0
AND
start_dev > '2013-03-11 11:46:48'
AND
start_dev = (SELECT MIN(start_dev) FROM start_stop AS a1 WHERE a0.carrier_id = a1.carrier_id AND DATE(a1.start_dev) = DATE(a0.start_dev))
AND IsNotificationInSchedule(22, start_dev) > 0
) UNION ALL (
SELECT
NULL AS alertStart,
UNIX_TIMESTAMP(CONVERT_TZ(stop_dev, '+00:00', GetCarrierTimezone(a0.carrier_id))) AS alertStop,
c0.name AS carrier_name,
carrier_image,
l0.Latitude,
l0.Longitude
FROM
start_stop AS a0
INNER JOIN carriers AS c0 ON a0.carrier_id = c0.id
INNER JOIN pcoarg AS l0 ON a0.stopLogId = l0.id
WHERE
FIND_IN_SET(a0.carrier_id, '89467,1,64578,222625,45013') > 0
AND
stop_dev > '2013-03-11 11:46:48'
AND
stop_dev = (SELECT MAX(stop_dev) FROM start_stop AS a1 WHERE a0.carrier_id = a1.carrier_id AND DATE(a1.stop_dev) = DATE(a0.stop_dev))
AND IsNotificationInSchedule(22, start_dev) > 0
)
) AS startStops
ORDER BY IF(alertStart IS NULL, alertStop, alertStart)
Here is EXPLAIN on single query:
1 PRIMARY c0 ALL PRIMARY 17 Using where
1 PRIMARY a0 ref PRIMARY,startstop_carriers_stopdev_idx,georefidx,startstop_carriers_startdev_idx startstop_carriers_stopdev_idx 4 test_backoffice.c0.id 72 Using where
1 PRIMARY l0 ref id ASC id ASC 4 test_backoffice.a0.startLogId 1 Using where
2 DEPENDENT SUBQUERY a1 ref PRIMARY,startstop_carriers_stopdev_idx,georefidx,startstop_carriers_startdev_idx startstop_carriers_stopdev_idx 4 test_backoffice.a0.carrier_id 72 Using where; Using index
And here is the EXPLAIN for the JOIN:
1 PRIMARY <derived2> system 0 const row not found
2 DERIVED c0 ALL PRIMARY 17 Using where
2 DERIVED a0 ref PRIMARY,startstop_carriers_stopdev_idx,georefidx,startstop_carriers_startdev_idx startstop_carriers_stopdev_idx 4 test_backoffice.c0.id 72 Using where
2 DERIVED l0 ref id ASC id ASC 4 test_backoffice.a0.startLogId 1 Using where
3 DEPENDENT SUBQUERY a1 ref PRIMARY,startstop_carriers_stopdev_idx,georefidx,startstop_carriers_startdev_idx startstop_carriers_stopdev_idx 4 test_backoffice.a0.carrier_id 72 Using where; Using index
4 UNION c0 ALL PRIMARY 17 Using where
4 UNION a0 ref PRIMARY,startstop_carriers_stopdev_idx,georefidx,startstop_carriers_startdev_idx startstop_carriers_stopdev_idx 4 test_backoffice.c0.id 72 Using where
4 UNION l0 ref id ASC id ASC 4 test_backoffice.a0.stopLogId 1 Using where
5 DEPENDENT SUBQUERY a1 ref PRIMARY,startstop_carriers_stopdev_idx,georefidx,startstop_carriers_startdev_idx startstop_carriers_stopdev_idx 4 test_backoffice.a0.carrier_id 72 Using where; Using index
UNION RESULT <union2,4> ALL
Help on this one would be greatly appreciated. 🙂
EDIT:
I'm getting inconsistent result. If I remove the convert_tz for example and try to get the timezone outside the union I get very fast results, but If I rename the result it automatically goes down to the same underperformante query:
SELECT
*,
GetCarrierTimezone(carrier_id) timezone
FROM
(
this takes 0.374s
SELECT
*,
GetCarrierTimezone(carrier_id)
FROM
(
while this takes 0.078 (mostly the lag from the db to my machine)..
Best Answer
I would expect this to happen because of the ORDER BY you have in there.
Try this in the first part of the UNION:
And this in the second part:
And then replace the
ORDER BY
withIn other words, remove the need for the IF in the order by.