I have a query with 4 tables and plain 'JOIN's
the explain gives the best join order, and it completes in 1.5 secs
I add a single ORDER BY (a calculated column) and the join orders all shift
and the query takes 85secs!
So I read the docs and it suggests STRAIGHT_JOIN to force join order.
now I was using:
JOIN myTable ON xyx=abc
but in the docs it seems the ON condition is not permisible here, though it
does work.
Am I infact doing an 'INNER JOIN'? certainly if I change to INNER JOIN there
is no difference.
However the only way I can force the join order is to use STRAIGHT_JOIN that
does not accept an ON condition, so I have shifted the clauses to the WHERE
and it works fine.
Is there any syntax I can use to keep the ON conditions, I prefer this
approach it makes the code clearer regarding intent.
Here's the SELECT:
SELECT
place.id, pow((place.x*100)-423302,2)+pow((place.y*100)-381522,2) AS
distance,
place.name, place.x, place.y
FROM place
STRAIGHT_JOIN placeNeighbours AS n ON place.id = n.place1
STRAIGHT_JOIN place AS place2 ON
pow((place.x*100)-(place2.x*100),2)+pow((place.y*100)-(place2.y*100),2) <
1600 AND place2.id = n.place2
STRAIGHT_JOIN shop ON shop.place = place2.id
GROUP BY place.id
HAVING distance <= 1.6E7
ORDER BY distance
LIMIT 0,10
--
Mike W
