Hi,
I have a problem with a very slow query that should be VERY fast. There are two tables: CREATE TABLE `tfbs_mm`.`tfbs_ecr` ( `tfbs_id` int(10) unsigned NOT NULL, `ecr_id` int(10) unsigned NOT NULL, KEY `tfbs_id` (`tfbs_id`), KEY `ecr_id` (`ecr_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; Has ~101 Mio entries CREATE TABLE `tfbs_mm`.`ecr_neighbor_knowngene` ( `ecr_id` int(10) unsigned NOT NULL, `distance_5` int(10) unsigned default NULL, `gene_5_id` int(10) unsigned default NULL, `distance_3` int(10) unsigned default NULL, `gene_3_id` int(10) unsigned default NULL, `identity` int(10) unsigned default NULL, PRIMARY KEY (`ecr_id`), KEY `5_3_ident` (`gene_5_id`,`gene_3_id`,`identity`), KEY `5_dist` (`gene_5_id`,`distance_5`), KEY `3_dist` (`gene_3_id`,`distance_3`), KEY `5_ident` (`gene_5_id`,`identity`), KEY `3_ident` (`gene_3_id`,`identity`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; Has ~1.2 Mio entries SELECT ecr_id FROM ecr_neighbor_knowngene e where gene_5_id = 3 or gene_3_id = 3; returns 25 entries within a few milliseconds explain select * from tfbs_ecr t 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); * uses key ecr_id select * from tfbs_ecr t 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. When I combine them both I get an extremely slow query: 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); 1, 'PRIMARY', 't', 'ALL', '', '', '', '', 101129498, 'Using where' 2, 'DEPENDENT SUBQUERY', 'e', 'unique_subquery', 'PRIMARY,5_3_ident,5_dist,3_dist,5_ident,3_ident', 'PRIMARY', '4', 'func', 1, 'Using where' Can somebody please explain to me why this happens and how I can avoid this? Thanks a lot, Bernd
