CODEWITHSUNDEEP
mysqljoinunion
Scott S
Scott S

Reputation: 90

Improve MySQL Union Statement

I have the following mySql statement, which I am sure can be made more efficient, I'm just not sure what would be the best way... the only difference is the equip_id that changes in the WHERE clause...

==============

 (SELECT 
        `receipt_ts`, 
        `driver_id`,
        `equip_id`, 
        `pos_lon`, 
        `pos_lat`, 
        `pos_timestamp`  
   FROM `log_messaging` 
  WHERE `equip_id`='207' AND `tran_type`='T.2.12.0' 
  ORDER BY receipt_ts DESC LIMIT 1 ) 

 UNION  

(SELECT 
       `receipt_ts`, 
       `driver_id`, 
       `equip_id`, 
       `pos_lon`, 
       `pos_lat`, 
       `pos_timestamp`  
  FROM `log_messaging` 
 WHERE `equip_id`='212' AND `tran_type`='T.2.12.0' 
 ORDER BY receipt_ts DESC LIMIT 1 ) 

 UNION

  (SELECT 
         `receipt_ts`, 
         `driver_id`, 
         `equip_id`, 
         `pos_lon`, 
         `pos_lat`, 
         `pos_timestamp`  
    FROM `log_messaging`
   WHERE `equip_id`='213' AND `tran_type`='T.2.12.0' 
   ORDER BY receipt_ts DESC LIMIT 1 );

================

I am unioning each returned row to build a dataset of max(receipt_ts) for multiple equip_id's. (I need to get the most recent positioning for the equipment.

Sometimes the query ends up being for 100+ unique equip_id's.

I am trying to make the query execute quicker than it currently is (about 7 seconds for ~100 UNIONS as above...

Point me in the right direction??

Thanks!

Upvotes: 1

Views: 46

Answers (2)

Jean-François Savard
Jean-François Savard

Reputation: 21004

I would use the IN clause : 

SELECT receipt_ts, driver_id, equip_id, pos_lon, pos_lat, pos_timestamp 
FROM log_messaging a
JOIN (SELECT c.equip_id, max(c.receipt_ts) as receipt
      FROM log_messaging c
      WHERE equip_id in ('207', '212', '213')
        AND tran_type='T.2.12.0'
      GROUP by c.equip_id) b USING(equip_id)
WHERE b.receipt = a.receipt_ts
ORDER BY a.receipt_ts

Note that if you really want to use the UNION (I don't see why you would) but want to optimize it, you could use the UNION ALL which would be more performent as UNION check datas for duplicata removal which consume more process.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269583

First, you should use union all rather than union. So start with this query:

(SELECT receipt_ts, driver_id, equip_id, pos_lon, pos_lat, pos_timestamp
 FROM log_messaging
 WHERE equip_id = '207' AND tran_type = 'T.2.12.0'
 ORDER BY receipt_ts DESC
 LIMIT 1
) UNION ALL
(SELECT receipt_ts, driver_id, equip_id, pos_lon, pos_lat, pos_timestamp
 FROM log_messaging
 WHERE equip_id = '212' AND tran_type = 'T.2.12.0'
 ORDER BY receipt_ts DESC
 LIMIT 1
) UNION ALL
(SELECT receipt_ts, driver_id, equip_id, pos_lon, pos_lat, pos_timestamp
 FROM log_messaging
 WHERE equip_id = '213' AND tran_type = 'T.2.12.0'
 ORDER BY receipt_ts DESC
 LIMIT 1
) ;

This is a reasonable query. I would suggest that you create an index on log_messaging(tran_type, equip_id, recipt_ts).

Upvotes: 1

Related Questions