MySQL Forums
Forum List  »  Performance

Join and order by between two tables in two databases
Posted by: Francois Debly
Date: April 06, 2005 02:18AM

Hello,

I have experienced a problem by joining tables in two different databases.

First case (that works): I have 3 tables in the same database (test). I execute this query:

SELECT du.id,ug.sex,ug.birthyear,ug.postcode,du.keywords,du.hairColor,du.eyesColor,abs(ug.birthyear-(1980-3)) as agePrior,(du.photo_id > 0) as photo, (di.status = 'GRANTED') as photoPrior FROM DatingUser AS du LEFT JOIN User AS ug ON (ug.id=du.id) LEFT JOIN DatingImage AS di ON (di.imageid=du.photo_id) WHERE du.id !=666397 AND ug.birthyear <= 1987 AND ( du.searchedSex ='M' OR du.searchedSex='B') AND ug.sex='M' AND ( ug.sex='M' OR ug.birthyear BETWEEN 1976 AND 1980 OR ug.postcode like '38%') ORDER BY photo Desc, photoPrior Desc, agePrior , ug.sex DESC, ug.birthyear desc LIMIT 44;

The execution time is about 2s and the indexes are optimized.

Second case: now I move (data+indexes) the table User into a second database (test2) and I perform the same request:

SELECT du.id,ug.sex,ug.birthyear,ug.postcode,du.keywords,du.hairColor,du.eyesColor,abs(ug.birthyear-(1980-3)) as agePrior,(du.photo_id > 0) as photo, (di.status = 'GRANTED') as photoPrior FROM DatingUser AS du LEFT JOIN test2.User AS ug ON (ug.id=du.id) LEFT JOIN DatingImage AS di ON (di.imageid=du.photo_id) WHERE du.id !=666397 AND ug.birthyear <= 1987 AND ( du.searchedSex ='M' OR du.searchedSex='B') AND ug.sex='M' AND ( ug.sex='M' OR ug.birthyear BETWEEN 1976 AND 1980 OR ug.postcode like '38%') ORDER BY photo Desc, photoPrior Desc, agePrior , ug.sex DESC, ug.birthyear desc LIMIT 44;

The execution time is now about 4 minutes (time after several executions remains the same).



This difference between the two requests does not appear if I remove the "order by" clause.



Someone can explain that ?


Many, many thanks in advance,


Francois.

Options: ReplyQuote


Subject
Views
Written By
Posted
Join and order by between two tables in two databases
7112
April 06, 2005 02:18AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.