[prev in list] [next in list] [prev in thread] [next in thread] 

List:       mysql
Subject:    Re: index not used
From:       Baron Schwartz <baron () xaprb ! com>
Date:       2007-05-29 20:10:28
Message-ID: 465C88B4.8040908 () xaprb ! com
[Download RAW message or body]

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/mysql?unsub=mysql-marcsub@progressive-comp.com

[prev in list] [next in list] [prev in thread] [next in thread]