Hi Bernd,
Bernd Jagla wrote:
Hi,
I have a problem with a very slow query that should be VERY fast.
> select * from tfbs_ecr t
[snip]
where ecr_id in
(301,302,316,323,345,346,351,362,363,364,380,411,413,451,464,466,470,471,481
,498,506,507,538,541,543);
=> returns 1167 rows within a few milli seconds.
[snip]
explain select * from tfbs_ecr t
where ecr_id in (SELECT ecr_id FROM ecr_neighbor_knowngene e
where gene_5_id = 3 or gene_3_id = 3);
MySQL mis-optimizes some IN and NOT IN subqueries to be dependent, so they are not
executed first, but are run for each row in the outer table. You should generally try
to rewrite the query as a JOIN. This is a common surprise to those new to MySQL.
Baron
--
Baron Schwartz
http://www.xaprb.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
