Hello,
I'm using MySQL 4.1.7 and want to use subqueries (very useful !).
All the queries i'm trying (with subqueries) give the correct results,
but have so bad performances that it is impossible to use it.
I did many tests, and it seems to be a general comportement for MySQL,
not particular to my datas. It is for me a big problem, as it is not possible
to write every useful query without using subqueries.
Some examples :
1) IN
I have 2 very simple requests that MySQL executes very fast.
select id from table2 where ... (0.00 sec)
select * from table1 where col1 in ( result of above query, hardcoded );
(0.00 sec).
If i write this in a single query
select * from table1 where col1 in (select id from table2 where ...);
it takes lot of times (many seconds !). It is difficult for me to understand
that
it can take more than the sum of the alone queries. How can MySQL takes
1000x this time ?
2) exist
I take 2 single tables. The 2 requests seems to me as identical (col1 is
unique).
select * from t1 where exists(select col1 from t2 where t1.col1=t2.col1
and t2.col2=myValue);
select * from t1,t2 where t1.col1=t2.col1 and t2.col2=myValue;
The first one (with subquery) takes again many seconds, while the second is
absolutely straightforward (0.00 sec).
In the second case, MySQL reads t2 first (uses the where and the index), then
t1.
With subqueries, MySQL seems to always scan the first table, and then go to
the second. It then must scan all my t1 table :-(
Can somebody explain this ?
Can this be considered as bugs in MySQL ?
Can we hope this to be fixed soon ?
I naturally can give more details, give content of tables that have the
problem, ...
but the patterns i wrote seems to always have the problem. You only have to
avoid too little
table, so that you can see something.
Thank you.
