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.

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)..

share|improve this question
    
The simplest would be to run them separately and combine the results in the application. –  ypercube Mar 12 '13 at 20:45
    
hi @ypercube, that crossed my mind :) but its so ugly to do that and maintain that code. Besides I's still have to sort the results in php. –  helderjsm Mar 12 '13 at 20:48
    
I meant run the 2 queries with the wanted sort. Then you only need to merge in php (no sorting). –  ypercube Mar 12 '13 at 20:49
1  
The sorting is not linear. The result of query 1 can be in between the results of query 2. –  helderjsm Mar 12 '13 at 20:53
1  
I don't think @ypercube is assuming the results don't overlap: a 'merge' is much cheaper/easier than a sort to implement in php. Of course fixing the problem in the SQL if possible would be a far better solution :) –  Jack Douglas Mar 13 '13 at 6:52
show 3 more comments

migrated from stackoverflow.com Mar 12 '13 at 20:26

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

1 Answer

In a very very similar case, i noticed from process list of mysql the very bad behaviour of 'copy to temp table' (copying What? I don't know). I think mysql tempted a 'best approach' for querying, but in this case failed, so using code to 'merge' 2-query results worked fine.

share|improve this answer
    
Hi realtebo, Thanks for the input. This is a bit old now, but for what I remember the inconsistency was because some how mysql was caching some results and not others. I eventually recreated the query in a more efficient way specially by keeping track of the values I needed in a separate table making the indices more efficient. –  helderjsm Aug 13 '13 at 8:54
add 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.