Results 1 to 3 of 3
Thread: query optimalization problem
Jun 10, 2013, 02:08 #1
- Join Date
- Nov 2004
- Nederland, Eindhoven
- 0 Post(s)
- 0 Thread(s)
query optimalization problem
I've a problem with a query that doesn't preform well and I've tried a bunch of things, but can't get it right. I'd like to know if I'm trying it wrong or that I just want the impossible.
Below I have relevant table data / indexes, my queries and the explain results.
I want to select gps points from a full day. The points are organised by segments in a track. These tracks obviously span across days and can be of ignition on or off type. Now cirtain units don't post points if they are off, so the first point in a day can be an active (moving) point and this leads to problems with reports about start en stop times. So I want all points from a day (easy) and all points from a ignition-off track ending on that day (easy). But I can't seem te get the together with good performance. Somehow mysql always selects all previous points...
id gps_track_segment_id gps_unit_id date indices: primary gps_track_segment_id gps_unit_date_idx
id gps_track_id date indices primary gps_track_id
id hash (another unique identifier) gps_unit_id startdate stopdate ignition indices primary gps_unit_id hash
EXPLAIN SELECT gps_points.id AS gps_point_id, gps_points.date, gps_points.ignition, ROUND((gps_points.lat / 1000000),6) AS lat, ROUND((gps_points.lng / 1000000),6) AS lng, UNIX_TIMESTAMP(gps_points.date) AS time FROM gps_points INNER JOIN gps_units ON gps_units.id = gps_points.gps_unit_id LEFT JOIN gps_track_segments ON gps_track_segments.id = gps_points.gps_track_segment_id LEFT JOIN gps_tracks ON gps_tracks.id = gps_track_segments.gps_track_id WHERE ( gps_points.date BETWEEN "2013-01-09 00:00:00" AND "2013-01-09 23:59:59" AND gps_points.gps_unit_id = "498" AND gps_units.relation_id = "93" ) OR (gps_tracks.startdate <= "2013-01-09 00:00:00" AND gps_tracks.stopdate BETWEEN "2013-01-09 00:00:00" AND "2013-01-09 23:59:59" AND gps_tracks.ignition = 0 AND gps_points.gps_unit_id = "498" AND gps_tracks.gps_unit_id = "498" ) ORDER BY gps_points.date ASC
This query is rather slow and the explain looks like:
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE gps_units const PRIMARY,relation_id PRIMARY 4 const 1 1 SIMPLE gps_points ref date,gps_unit_id,gps_unit_date_idx gps_unit_date_idx 4 const 22484 Using where 1 SIMPLE gps_track_segments eq_ref PRIMARY PRIMARY 4 gps_points.gps_track_segment_id 1 1 SIMPLE gps_tracks eq_ref PRIMARY PRIMARY 4 gps_track_segments.gps_track_id 1 Using where
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE gps_units const PRIMARY PRIMARY 4 const 1 Using index; Using temporary; Using filesort 1 SIMPLE gps_tracks ref PRIMARY,gps_unit_id gps_unit_id 4 const 1311 Using where 1 SIMPLE gps_track_segments ref PRIMARY,gps_track_id gps_track_id 4 gps_tracks.id 4 Using where 1 SIMPLE gps_points ref gps_track_segment_id,gps_unit_id,segment_date_idx,gps_unit_date_idx segment_date_idx 4 gps_track_segments.id 4 Using where
Any ideas would be welcome.What good are one-liners if they don't