Announcement

Announcement Module
Collapse
No announcement yet.

query optimisation when ref is NULL with explain

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

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

  • #2
    Do you have indexes on your table? What is table structure?

    Comment


    • #3
      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 | |
      +-------------+----------------------+------+-----+--------- +-------+

      Comment

      Working...
      X