| query optimisation when ref is NULL with explain [message #3165] |
Mon, 09 June 2008 08:01  |
 jduartefr
Messages: 2
Registered: June 2008
Location: Clermont-Ferrand |
Junior Member |
|
|
Hello,
i'm trying to accelerate some queries from the tool est2uni freely downloadable online.
Here is one for which i don't understand why the ref is NULL when i run the explain comamnd:
mysql> explain SELECT clone.library,COUNT(*) AS num_clean_est,AVG(sequence.length) AS mean_length,STD(sequence.length) AS std_dev FROM clone,est,sequence WHERE clone.name=est.clone AND est.processed_seq=sequence.name GROUP BY clone.library;
+----+-------------+----------+--------+-------------------- -+---------+---------+-------------------------------------+ ---------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+--------+-------------------- -+---------+---------+-------------------------------------+ ---------+---------------------------------+
| 1 | SIMPLE | clone | ALL | PRIMARY | NULL | NULL | NULL | 1055065 | Using temporary; Using filesort |
| 1 | SIMPLE | est | ref | clone,processed_seq | clone | 15 | triticum_aestivum.clone.name | 1 | Using where |
| 1 | SIMPLE | sequence | eq_ref | PRIMARY | PRIMARY | 20 | triticum_aestivum.est.processed_seq | 1 | Using where |
+----+-------------+----------+--------+-------------------- -+---------+---------+-------------------------------------+ ---------+---------------------------------+
3 rows in set (0.01 sec)
Is there a way to improve this query ?
Many thanks for your help
jorge.
|
|
|
|
| Re: query optimisation when ref is NULL with explain [message #3167 is a reply to message #3166 ] |
Tue, 10 June 2008 04:21  |
jduartefr
Messages: 2
Registered: June 2008
Location: Clermont-Ferrand |
Junior Member |
|
|
Yes i have indexes, eg.
table clone:
+---------------+----------------------+------+-----+------- --+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+----------------------+------+-----+------- --+-------+
| name | char(40) | | PRI | | |
| insert_length | smallint(5) unsigned | YES | | NULL | |
| library | char(255) | YES | MUL | NULL | |
| researcher_id | char(40) | YES | | NULL | |
| institute | char(40) | YES | | NULL | |
| biblio_ref_id | int(10) unsigned | YES | | NULL | |
+---------------+----------------------+------+-----+------- --+-------+
table est
+-----------------+------------------+------+-----+--------- +-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+------------------+------+-----+--------- +-------+
| name | varchar(40) | | PRI | | |
| clone | varchar(40) | | MUL | | |
| raw_seq | varchar(40) | | | | |
| processed_seq | varchar(40) | YES | MUL | NULL | |
| researcher_id | varchar(40) | YES | | NULL | |
| institute | varchar(40) | YES | | NULL | |
| biblio_ref_id | int(10) unsigned | YES | | NULL | |
| unigene | varchar(40) | YES | MUL | NULL | |
| location_begin | smallint(6) | YES | | NULL | |
| location_end | smallint(6) | YES | | NULL | |
| r_begin_in | smallint(6) | YES | | NULL | |
| r_end_in | smallint(6) | YES | | NULL | |
| inserts | mediumtext | YES | | NULL | |
| orientation_fwd | tinyint(1) | YES | | NULL | |
+-----------------+------------------+------+-----+--------- +-------+
table sequence
+-------------+----------------------+------+-----+--------- +-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------------------+------+-----+--------- +-------+
| name | varchar(40) | | PRI | | |
| length | smallint(5) unsigned | | | 0 | |
| sequence | mediumtext | | | | |
| quality | text | YES | | NULL | |
| annotation | tinytext | YES | | NULL | |
| object_type | varchar(10) | | MUL | | |
| clone | varchar(40) | YES | MUL | NULL | |
| full_length | char(3) | YES | | NULL | |
+-------------+----------------------+------+-----+--------- +-------+
|
|
|
Members
Search
Help
Register
Login
Home
