1

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 Jan 24 '17 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 Jan 24 '17 at 5:10
  • Please provide SHOW CREATE TABLE and EXPLAIN SELECT ... – Rick James Jan 24 '17 at 16:50
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.

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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