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.
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.
Comment