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!
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.
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句