Hi!
mathias brandt wrote:
[[...]]
the statement
SELECT id FROM table1 WHERE cityname = 'bla' (cityname has been indexed)
takes 0.0002 seconds and returns 0 rows, which was expected.
the statement:
SELECT id from table1 WHERE id IN ( SELECT id FROM table1 WHERE cityname = 'bla
)
still returns 0 rows but takes 12.9 seconds!!
The first statement uses an equality condition for a single on an index,
the fastest thing possible.
The second statement can do the same in its subquery. But then, it gets
(possibly) a list of values returned, and for such a list there are two
general possibilities:
a) Loop over each element in the list, comparing it to "id" in the outer
SELECT;
b) Scan the table for the outer SELECT, comparing "id" to the values in
the list.
If "id" is not supported by an index, strategy a) is impossible.
Assuming that the table has got an index for "id" (which was not said),
strategy a) might be the more effective for short returned lists - but
in general, it will not be.
An "IN" condition is (semantically) equivalent to an "OR" expression
which is notorious hard to optimize.
Mathias, if you want to pursue this further, you should at least specify
the version you are using and show the full table definition, including
all indices.
Leaving aside that the nesting in the second statement makes no sense at
all ("SELECT id WHERE id IN <subselect>" is the same as "<subselect>"),
I still fear such a construct will never be fast.
Remember that for any given "cityname" there may be multiple "id"
values, and the execution plan has to allow for that. Only if you can
guarantee there is only one "id", try to replace the "IN" by a "=".
HTH,
Jörg
--
Joerg Bruehe, Senior Production Engineer
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
