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
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
