>> select * from table where table_id in (?, ?, ?, ?, ?, ?, ?, ...)
I usually try to rewrite this kind of queries to
select whatever from table t1 join
(select table_id from xxxxx where xxxxx) t2 using (table_id)
Because the results would be different than a subselect, less work = faster. One thing to point out is that a query of the form:
Would normally result in a SORT UNIQUE for the "select id from bar where n=27" part. Where as:
select ...
from foo f1, (select id from bar where n=27) f2
where f1.id = f2.id
is the same as...
select ...
from foo f1, bar f2
where f2.n=27
and f1.id=f2.id
which would not result in a sort unique. In order to obtain the same results as a subselect you would need to group or distinct, and I would imagine the results would be the same as the IN..SUBSELECT
aaah, you are right. My rewriting only works when the "id" column is a primary key in the subqueried table; that way guaranteed to be unique, so that
select distinct id from whatever where whateverelse
yields the same results as
select id from whatever where whateverelse
thanks for pointing it out,
Harald
--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Reinsburgstraße 202b
70197 Stuttgart
0173/9409607
fx 01212-5-13695179
-
Python: the only language with more web frameworks than keywords.
