Uncorrelated subqueries and indexes
Posted by:
Dave G
Date: June 21, 2008 04:47PM
Hello,
I have a third party product that is connecting to MySQL and I'm having some issues with the queries it is generating. They seem simple enough - but they take a large amount of time
The queries look something like (apologies, I don't have the real SQL/explain output in front of me).
SELECT * FROM(
SELECT sum(x), table1_key1,table1_key2
FROM table1) t1, (
SELECT sum(y), table2_key1,table2_key2
FROM table2) t2
WHERE t2.table2_key1 = t1.table1_key1
AND t2.table2_key2 = t1.table1_key2
where tableX_keyY both have indexes on their respective tables. Assuming they have 1 million rows each, it would seem that, since they are uncorrelated, they would run quickly and the resulting join. If I EXPLAIN it, it doesn't look like the indexes are being used at all. Is there anything I can do, without modifying the query, to speed this up? Is this a shortcoming of MySQL or am I missing something more fundamental?
thanks!