1 to 1 relationship wrong way round?

[thompom]

Hi,

The query below is v.slow - i think this is beacuse the relationship is the "wrong way round"
can anyone confirm this or suggest an alternative

SELECT event.eventid, finprop.finpropid
FROM event 
INNER JOIN action 
ON action.eventid = event.eventid 
INNER JOIN eventstocklink 
ON eventstocklink.eventstocklinkid = action.eventstocklinkid 
INNER JOIN finprop 
ON finprop.eventstocklinkid = eventstocklink.eventstocklinkid

the relationship between eventstocklink and finprop is 1 - 1

i think the query would run better if it was:

SELECT event.eventid, finprop.finpropid
FROM event 
INNER JOIN action 
ON action.eventid = event.eventid 
INNER JOIN eventstocklink 
ON eventstocklink.eventstocklinkid = action.eventstocklinkid 
INNER JOIN finprop 
ON finprop.finpropid = eventstocklink.finpropid

 

[r937]

this makes no sense unless we can see either 1) the SHOW CREATE TABLE statements ~including~ foreign keys, or 2) representative sample data

and what does "run better" mean?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[DonQuichote]

It would surprise me if a 1:1 relation actually could be the wrong way around, unless one of the tables is very sparse.

It is far more likely that there is an index missing.

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)