Join and order by between two tables in two databases

[Francois Debly]

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.