[BioSQL-l] Fwd: BioSQL - MySQL index usage

[Giles Weaver]

I've also found the performance of queries across the taxon and taxon_name
tables to be inadequate on occasion. My solutions were:
a) to remove all taxa outside of my area of interest from the database, and
remove non scientific names from taxon_name
b) to create a temporary table (join of taxon and taxon_name) of my taxa of
interest and query against that instead of the taxon and taxon_name tables.

I can't remember which of these was more effective, performance wise, but
obviously neither is an ideal solution.

2009/2/11 Renato Alves <rjalves at igc.gulbenkian.pt>

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
> _______________________________________________
> BioSQL-l mailing list
> BioSQL-l at lists.open-bio.org
> http://lists.open-bio.org/mailman/listinfo/biosql-l
>