This makes no sense to me, please explain if you can. I have two
queries, both run in under a tenth of a second. But, when one is run
as a sub-query of the other, run time is essentially infinite ( I
haven't had that much patience, yet ).
For example, although these are not the actual queries;
Query A = SELECT id FROM table WHERE key < 10
Query B = SELECT row FROM other-table WHERE id IN
( 0,1,2,3,4,5,6,7,8,9 )
Each query runs and returns almost immediately, separately ( and
A returns 0-9 ).
Query C = SELECT row FROM other-table WHERE id IN ( Query A )
That one goes away and never comes back, as far as I know.
How can query C take forever, when composed of two such fast
queries? What is done differently when it's run as sub-query, other
than a copy to tmp table? Speaking "copy to tmp table" is the state
that query C stays in forever, even when it's only supposed to be
copying 10 tiny integers. All the appropriate indices are in place,
and explain claims to be using them correctly. There are a few
million rows in the respective tables, but it still doesn't add up.
What's going on? Thanks. ( MySQL 4.1.14 on various systems )
--
David Hillman
LiveText, Inc
1.866.LiveText x235
