List:General Discussion« Previous MessageNext Message »
From:Olga Lyashevska Date:October 30 2009 10:39am
Subject:Re: self-joins in hierarchical queries: optimization problem
View as plain text  
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



Thread
self-joins in hierarchical queries: optimization problemOlga Lyashevska29 Oct
  • Re: self-joins in hierarchical queries: optimization problemkabel29 Oct
  • Re: self-joins in hierarchical queries: optimization problemSergey Petrunya29 Oct
    • Re: self-joins in hierarchical queries: optimization problemSergey Petrunya29 Oct
  • Re: self-joins in hierarchical queries: optimization problemMikhail Berman29 Oct
    • Re: self-joins in hierarchical queries: optimization problemOlga Lyashevska30 Oct
Re: self-joins in hierarchical queries: optimization problemOlga Lyashevska29 Oct