| 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.
|
|
|
Members
Search
Help
Register
Login
Home
