From: Olga Lyashevska (olgaherenstraat.nl)
Date: Fri Oct 30 2009 - 05:39:36 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

Dear Michail and Sergey,

Thank you very much for your responses and kind suggestions!

On 29.10.2009, at 16:53, Sergey Petrunya wrote:

> this makes it clear that index on O1.tsn will not be useful. You
> need indexes
> on parent_tsn column.

mysql> alter table taxonomic_units1 add index (parent_tsn);
Query OK, 483305 rows affected (7.76 sec)
Records: 483305 Duplicates: 0 Warnings: 0

And it is solved! It works like a charm!

mysql> CREATE TABLE flatfile
     -> SELECT O1.name AS tclass, O2.name AS torder, O4.name AS
tfamily, O5.name AS tgenus, O6.name AS tspecies
     -> FROM taxonomic_units1 AS O1
     -> LEFT OUTER JOIN
     -> taxonomic_units1 AS O2
     -> ON O1.tsn = O2.parent_tsn
     -> LEFT OUTER JOIN
     -> taxonomic_units1 AS O3
     -> ON O2.tsn = O3.parent_tsn
     -> LEFT OUTER JOIN
     -> taxonomic_units1 AS O4
     -> ON O3.tsn = O4.parent_tsn
     -> LEFT OUTER JOIN
     -> taxonomic_units1 AS O5
     -> ON O4.tsn = O5.parent_tsn
     -> LEFT OUTER JOIN
     -> taxonomic_units1 AS O6
     -> ON O5.tsn = O6.parent_tsn;
Query OK, 2051444 rows affected (2 min 10.96 sec)
Records: 2051444 Duplicates: 0 Warnings: 0

My next task here is to match tspecies with another list of species
from a different table and display only those which appear in both
tables.
It can be done:
mysql> select flatfile.tclass, flatfile.torder, flatfile.tfamily,
flatfile.tgenus, flatfile.tspecies from flatfile, marinespecies where
tspecies=speciesmarine;

I wonder if it is not a better idea to incorporate this query into the
first one, perhaps in a form of subquery?

Thanks again,
Olga

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]