title: Optimise sql query
body: <p>I have got query taking up to 40 secs to execute and I was wondering how to make it faster...</p>
  
  <pre><code>SELECT rp.`id` as rip,r.`id`, 
    rl.`id` as rlid, r.`number`, r.`type`, 
    a.`title` as atitle, a.`id` as aid, more, 
    more_valid 
  FROM 
    `R_l_p` rp INNER JOIN 
    `Rl` rl ON rp.`rl_id` = rl.`id` INNER JOIN 
    `R` r ON r.`id` = rl.`r_id` INNER JOIN 
    `A` a ON a.`id` = r.`a_id` 
    LEFT JOIN 
     (SELECT `type`, `v`, `r_id`, COUNT(*) AS more 
      FROM `R_l` 
      WHERE `type` = 'stream' AND `v` = 1 
      GROUP BY `r_id`) stm ON stm.`r_id` = r.`id`
    LEFT JOIN 
     (SELECT `type`, `v`, `r_id`, COUNT(*) AS more_valid 
      FROM `R_l` 
      WHERE `type` = 'stream' AND `v` = 0 
      GROUP BY `r_id`) morelink ON morelink.`r_id` = r.`id`
  WHERE rp.`link` = 'dead' AND rl.`type` = 'stream' 
  ORDER BY rip ASC 
  LIMIT 0, 1000
  </code></pre>
  
  <pre>
  +-----+--------------+---------------+---------+--------------------------+-------------+----------+---------------------------+--------+----------------------------------------------+
  | id  | select_type  |    table      |  type   |      possible_keys       |    key      | key_len  |           ref             | rows   |                    Extra                     |
  +-----+--------------+---------------+---------+--------------------------+-------------+----------+---------------------------+--------+----------------------------------------------+
  |  1  | PRIMARY      | r             | ALL     | PRIMARY,a_id             | NULL        | NULL     | NULL                      | 21804  | Using temporary; Using filesort              |
  |  1  | PRIMARY      | a             | eq_ref  | PRIMARY                  | PRIMARY     | 4        | db453988339.r.a_id        |     1  |                                              |
  |  1  | PRIMARY      | rl            | ref     | PRIMARY,r_id,type        | r_id        | 4        | db453988339.r.id          |     1  | Using where                                  |
  |  1  | PRIMARY      | derived2      | ALL     | NULL                     | NULL        | NULL     | NULL                      | 21077  |                                              |
  |  1  | PRIMARY      | derived3      | ALL     | NULL                     | NULL        | NULL     | NULL                      |     1  |                                              |
  |  1  | PRIMARY      | rp            | eq_ref  | rl_id                    | rl_id       | 4        | db453988339.rl.id         |     1  | Using where                                  |
  |  3  | DERIVED      | R_link        | ALL     | type                     | NULL        | NULL     | NULL                      | 27580  | Using where; Using temporary; Using filesort |
  |  2  | DERIVED      | R_link        | ALL     | type                     | NULL        | NULL     | NULL                      | 27580  | Using where; Using temporary; Using filesort |
  +-----+--------------+---------------+---------+--------------------------+-------------+----------+---------------------------+--------+----------------------------------------------+
  </pre>
  
  <p>Thank you :)</p>