query optimisation when ref is NULL with explain

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.

Do you have indexes on your table? What is table structure?

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 | |
±------------±---------------------±-----±----±-------- ±------+