Re: self-joins in hierarchical queries: optimization problem
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
self-joins in hierarchical queries: optimization problem
Dear all, I have a table which contains taxonomic data (species, genera, family, order, class) and it is organized as adjacency list model. mysql select* from taxonomic_units1 limit 5; +-+---+-+ | tsn | name | parent_tsn | rank_id | +-+--+--+-+ | 50 | Bacteria |0 | 10 | | 51 | Schizomycetes 202421 | 60 | | 52 | Archangiaceae | 51 |140 | | 53 | Pseudomonadale | 51 | 100 | | 54 | Rhodobacteriineae | 53 | 110 | +-+-++-+ I am trying to flatten it, so that it can be used in further analysis (e.g. in R) I have been trying to run the following query, and it does what I want it to do, but it takes really long time to get it done. As a matter of fact I was not patient enough to get the whole output and instead set LIMIT 10. 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 LIMIT 10; +---+-+--+-- +---+ | tclass| torder | tfamily | tgenus | tspecies| +---+-+--+-- +---+ | Bacteria | NULL | NULL | NULL| NULL | | Schizomycetes | Archangiaceae| NULL | NULL| NULL | | Schizomycetes | Pseudomonadales | NULL | NULL | NULL| | Schizomycetes | Pseudomonadales | Nitrobacteraceae | Nitrobacter | Nitrobacteragilis | | Schizomycetes | Pseudomonadales | Nitrobacteraceae | Nitrobacter | Nitrobacterflavus| | Schizomycetes | Pseudomonadales | Nitrobacteraceae | Nitrobacter | Nitrobacteroligotrophis | | Schizomycetes | Pseudomonadales | Nitrobacteraceae | Nitrobacter | Nitrobacterpolytrophus | | Schizomycetes | Pseudomonadales | Nitrobacteraceae | Nitrobacter | Nitrobacterpunctata | I have checked this query with EXPLAIN, and it is not using any indices, even though column tsn is set as index in original table. ++-+---+--+---+--+- +--++---+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+--+---+--+- +--++---+ | 1 | SIMPLE | O1| ALL | NULL | NULL | NULL| NULL | 483305 | | | 1 | SIMPLE | O2| ALL | NULL | NULL | NULL| NULL | 483305 | | | 1 | SIMPLE | O3| ALL | NULL | NULL | NULL| NULL | 483305 | | | 1 | SIMPLE | O4| ALL | NULL | NULL | NULL| NULL | 483305 | | | 1 | SIMPLE | O5| ALL | NULL | NULL | NULL| NULL | 483305 | | | 1 | SIMPLE | O6| ALL | NULL | NULL | NULL| NULL | 483305 | | ++-+---+--+---+--+- +--++---+ 6 rows in set (0.00 sec) What is wrong with this query? Or is it a problem of all adjacency list models? Is there a way to get columns indexed using self-joins? Thanks, Olga -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: self-joins in hierarchical queries: optimization problem
Thanks Kabel, Not sure if this is the exact problem you're trying to solve, but this helped me in a similar situation. http://dev.mysql.com/tech-resources/articles/hierarchical-data.html Yes, I have seen this article before, and it is really nice. However they do not discuss any optimization issues. Now I was reading Joe Celko's book. Probably my query is simply not the best solution for this type of problem. Thanks, Olga -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
results of the query as a table
Dear all, I run a query and I try to save results as a table. On the results of this first query I want to run yet another query (perhaps a few). I have been trying to use CREATE VIEW statement, which works fine, except for the fact that fields are not indexed because as I understand it indices cannot be created on views. It really affects the performance, making it nearly impossible to run any further queries. I am aware that it is a rather trivial problem, but still I did not manage to find a solution which would meet my requirements. So my question is: are there any other possibilities to save results of the query as a table so that they will be re-used to run yet another query? Thanks in advance, Olga -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: results of the query as a table
On 27.10.2009, at 15:11, Steve Edberg wrote: At 2:59 PM + 10/27/09, Olga Lyashevska wrote: Dear all, I run a query and I try to save results as a table. On the results of this first query I want to run yet another query (perhaps a few). I have been trying to use CREATE VIEW statement, which works fine, except for the fact that fields are not indexed because as I understand it indices cannot be created on views. It really affects the performance, making it nearly impossible to run any further queries. I am aware that it is a rather trivial problem, but still I did not manage to find a solution which would meet my requirements. So my question is: are there any other possibilities to save results of the query as a table so that they will be re-used to run yet another query? Thanks in advance, Olga CREATE TABLE ... SELECT should do what you want. For example CREATE TABLE foo SELECT thing1,thing2,concat(thing3,thing4) as thing5 from bar where thing4 like 'baz%' order by thing1 desc You could create a TEMPORARY table if needed (CREATE TEMPORARY TABLE...). Assuming version 5.0: Thanks Steve. It is solved! Shall I add indices manually to speed up query? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: results of the query as a table
Dear Steve, On 27.10.2009, at 16:19, Steve Edberg wrote: Or you could do an ALTER TABLE afterwards to add appropriate indexes. And are you familiar with the EXPLAIN command to help optimize queries/decide what indexes to add? Thanks for this! I have added indicies with ALTER TABLE. And using EXPLAIN and ANALYZE TABLE I found out that in fact I am creating a huge Cartesian product joining fields of two tables which are not indexed! No wonder it took ages to get this query done, I used up 99% of CPU. Definitely it can and it should be optimized. Thanks for your tips again. Cheers, Olga -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
unable to change root password on Mac OSX 10.5.2
Dear List,
I have installed a binary package mysql-5.0.51a-osx10.5-x86_64.dmg
I can start up/shutdown MySQL and connect to MySQL server, but I cant
change a root password as required.
---begin example---
e7253:~ olichka$ cd /usr/local/mysql
e7253:mysql olichka$ sudo ./bin/mysqld_safe
Password:
Starting mysqld daemon with databases from
/usr/local/mysql-5.0.51a-osx10.5-x86_64/data
^Z
[1]+ Stopped sudo ./bin/mysqld_safe
e7253:mysql olichka$ bg
[1]+ sudo ./bin/mysqld_safe
e7253:mysql olichka$ /usr/local/mysql/bin/mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.51a MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql set password for 'root'@'localhost'=password('password');
ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'mysql'
---end example-
I can verify that server is running:
---begin example
e7253:mysql olichka$ bin/mysqladmin version
bin/mysqladmin Ver 8.41 Distrib 5.0.51a, for apple-darwin9.0.0b5 on i686
Copyright (C) 2000-2006 MySQL AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Server version 5.0.51a
Protocol version10
Connection Localhost via UNIX socket
UNIX socket /tmp/mysql.sock
Uptime: 4 min 41 sec
Threads: 1 Questions: 4 Slow queries: 0 Opens: 12 Flush tables: 1
Open tables: 6 Queries per second avg: 0.014
--end example---
And I can shutdown the server:
--begin example-
e7253:mysql olichka$ bin/mysqladmin -u root shutdown
e7253:mysql olichka$ STOPPING server from pid file
/usr/local/mysql-5.0.51a-osx10.5-x86_64/data/e7253.mb.qub.ac.uk.pid 080421
14:57:13 mysqld ended
-end example
Some tests:
-begin example--
e7253:mysql olichka$ bin/mysqlshow
++
| Databases |
++
| information_schema |
| test |
++
--end example---
As I understand, when I use a binary package I dont have to initialize the
grant tables manually (i.e. mysql_install_db). Hovever as I see above I
dont have a mysql db where all granted tables would be installed? I would
expect installer to run 'mysql_install_db' for me or not?
When I try to log in as a root, so I can change passwords:
I get:
--begin example--
e7253:/ olichka$ mysql -u root
-bash: mysql: command not found
-end example
Sorry for my ignorance, most probably I am doing something wrong, but I am
very new to both MySQL and UNIX.
Could you please suggest anything?
Thank you in advance,
Olga
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: unable to change root password on Mac OSX 10.5.2
Dear List, I have solved my problem I think. For some reasons 'alias mysql=/usr/local/mysql/bin/mysql' didnt work for me. When I type '/usr/local/mysql/bin/mysql -u root' I can log in as a root and change password. Later I can log in: '/usr/local/mysql/bin/mysql -u root -p' Thank you, Olga -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
