[BioSQL-l] BioSQL - MySQL index usage

Renato Alves rjalves at igc.gulbenkian.pt
Wed Feb 11 13:15:16 EST 2009


Greetings everyone,

I have been using BioSQL as the current platform to explore taxonomy and 
I must say I'm quite happy with it.

Recently however the complexity/ammount of queries we are using is 
raising an additional constraint, time.

After looking into way of optimizing the queries I noticed that in most 
cases the left_value, right_value (UNIQUE) indexes are not used even 
though they exist.

I did some quick tests and these were the results:

mysql> reset query cache; select * from taxon where left_value < 97224 
AND right_value > 97225;
(...)
7 rows in set (0.31 sec)

mysql> reset query cache; select * from taxon FORCE INDEX (left_value, 
right_value) where left_value < 97224 AND right_value > 97225;
(...)
7 rows in set (0.15 sec)

So my question is, does anyone know a faster way to achieve the same result?

Also if anyone knows how to make MySQL use the index without the 
explicit FORCE INDEX syntax I would be happy to give it a try.

Thanks,
Renato


More information about the BioSQL-l mailing list