469,631 Members | 991 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,631 developers. It's quick & easy.

straight_join, join order & join conditions

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
Jul 19 '05 #1
0 3388

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by VisionSet | last post: by
4 posts views Thread by Anthony Robinson | last post: by
reply views Thread by gr8white | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.