I have a
track and a
waypoint tables. The
track table is also connected to a bunch more tables that are irrelevent. I wish to get all tracks, where at least one of their waypoints is in specified bounds. The tracks should have all their waypoints, even ones outside the bounds. My query looks like this:
select <all columns> from track inner join waypoint point1 on point1.track_id = track.id left outer join other table on track.other_table_id = other_table.id <a few more tables outer joined> where track_id in( select distinct track_id from waypoint point2 where MBRContains(<POlYGON HERE>, point2) )
the efficient way to do this query would be first build a table of all track_ids from the subquery, then connect them to table entries, then left outer join other variables, and then inner join the waypoints from point1 table.
Instead, MySQL first gets a table of all
tracks, then outer joins, then does a lookup by both track_id and mbrcontains function, and then joins the inner waypoints.
How can I tell mysql to optimize the query?