[BioSQL-l] Sequence loading in biosql MySQL sooooo long

[Hilmar Lapp]

On 10/23/03 9:22 AM, "Eric Pelletier" <ericp at genoscope.cns.fr> wrote:

> I logged the queries on the MySQL server side, and observed that
> only a single query blocks the system for each entry :
> 
> SELECT name.name, node.node_rank, node.left_value FROM taxon
> node, taxon taxon, taxon_name name WHERE name.taxon_id =
> node.taxon_id AND taxon.left_value > node.left_value and
> taxon.left_value < node.right_value AND taxon.taxon_id = '1056'
> AND name.name_class = 'scientific name' ORDER by node.left_value;
> 
> 
> However, the same query, without the ORDER BY statement is quite
> fast.


Hmm - MySQL's query planner srews up completely. The ORDER BY should add a
millisecond in a very poor implementation, since it is about sorting 10-30
rows. So much for the world's fastest database ...

Which version of MySQL are you running? I suppose it's a rather recent one,
as I haven't had that problem with mine (3.23.54). Are you supposed to run
index and/or table statistics for that version once in a while, and you
forgot to do that? If you had been talking about Pg, that would have been
the most probable cause.

    -hilmar
-- 
-------------------------------------------------------------
Hilmar Lapp                            email: lapp at gnf.org
GNF, San Diego, Ca. 92121              phone: +1-858-812-1757
-------------------------------------------------------------