mysql query builder inefficiently builds a query

Question

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?

Answer 1

Short answer: You need to rewrite the query. Or maybe upgrade.

Long answer:

MySQL has long been dumb when it comes to IN ( SELECT ... ).

By using a derived table, the optimizer will do the subquery first, thereby being a lot more efficient:

SELECT ...
    FROM ( SELECT DISTINCT track_id
               FROM ...
               WHERE ... ) x
    JOIN ...
        ON x.track_id = ...

Or... Upgrade to 5.7 (or 8.0) with its "query rewrite" so that you can rewrite the query after it is sent from whatever is building it.

Answer 2

And the answer is STRAIGHT_JOIN

STRAIGHT_JOIN is similar to JOIN, except that the left table is always read before the right table. This can be used for those (few) cases for which the join optimizer puts the tables in the wrong order.

https://dev.mysql.com/doc/refman/5.7/en/join.html