Why aren't my indexes being utilised?

[gawi79]

Hi,

 

I'm trying to run the following query on my database (mysql version: 5.0.26):

 

select chrom_X.* from chrom_X,repeats_alu where repeats_alu.chr = "X" and (chrom_X.start >= repeats_alu.start and chrom_X.stop <= repeats_alu.stop);

 

Performing an EXPLAIN of the above query produces:

 

mysql> explain select * from chrom_X,repeats_alu where repeats_alu.chr = "X" and (chrom_X.start >= repeats_alu.start and chrom_X.stop <= repeats_alu.stop);

+----+-------------+-------------+------+----------------------------------+-----------+---------+-------+---------+------------------------------------------------+

| id | select_type | table      | type | possible_keys                    | key      | key_len | ref  | rows    | Extra                                          |

+----+-------------+-------------+------+----------------------------------+-----------+---------+-------+---------+------------------------------------------------+

|  1 | SIMPLE      | repeats_alu | ref  | chr_index,start_index,stop_index | chr_index | 4      | const |  42036 | Using where                                    |

|  1 | SIMPLE      | chrom_X    | ALL  | start_index,stop_index                | NULL      | NULL    | NULL  | 1245807 | Range checked for each record (index map: 0xC) |

+----+-------------+-------------+------+----------------------------------+-----------+---------+-------+---------+------------------------------------------------+

2 rows in set (0.00 sec)

 

I want to increase the speed of the query. Both tables contain approx 1 million rows. Unfortunately MySQL isn't utilising the start_index and stop_index in my tables to perform this query? Any ideas as to why this may be the case?

 

The table structures are below:

 

| chrom_X | CREATE TABLE `chrom_X` (

  `id` int(10) NOT NULL auto_increment,

  `chr` char(2) NOT NULL,

  `start` int(10) NOT NULL,

  `stop` int(10) NOT NULL,

  PRIMARY KEY  (`id`),

  KEY `chr` (`chr`),

  KEY `stop_index` (`stop`),

  KEY `start_index` (`start`)

) ENGINE=MyISAM AUTO_INCREMENT=1245808 DEFAULT CHARSET=latin1 |

 

 

| repeats_alu | CREATE TABLE `repeats_alu` (

  `id` int(7) NOT NULL auto_increment,

  `chr` varchar(2) NOT NULL,

  `start` int(10) NOT NULL,

  `stop` int(10) NOT NULL,

  `strand` int(1) NOT NULL,

  PRIMARY KEY  (`id`),

  KEY `chr_index` (`chr`),

  KEY `start_index` (`start`),

  KEY `stop_index` (`stop`)

) ENGINE=MyISAM AUTO_INCREMENT=1086476 DEFAULT CHARSET=latin1 |

 

 

Many Thanks

 

[revraz]

How many rows does the query return?

[gawi79]

Not sure as yet. The query is still running! I'll let you know once I have an answer. Thanks.

[revraz]

Well I know in Oracle, the Indexs are not used if a Function is used on the left side of the = sign, and if the result list is > 4% of the total rows, not sure if MySQL is the same or not.

[gawi79]

Hi,

 

In response to your previous post:

 

301372 rows in set (6 hours 21 min 19.76 sec)

 

Thanks.

[fenway]

That's because you have an inequality in your "join" condition!!!