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?

  • What, exactly, is point2?
    – Rick James
    Commented Jan 24, 2017 at 4:45
  • @RickJames point2 is a waypoint table to make sure that at least one waypoint is located in the provided bounds
    – f.khantsis
    Commented Jan 24, 2017 at 5:10
  • Please provide SHOW CREATE TABLE and EXPLAIN SELECT ...
    – Rick James
    Commented Jan 24, 2017 at 16:50

2 Answers 2


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:

               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.

  • it is 5.7. what is query rewrite?
    – f.khantsis
    Commented Jan 24, 2017 at 5:07
  • anyway, I rewrote it as you said, and it achieved nothing.
    – f.khantsis
    Commented Jan 24, 2017 at 6:31
  • Let's see EXPLAIN SELECT ...;
    – Rick James
    Commented Jan 24, 2017 at 16:51

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.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.