Optimise sql query

Question

I have got query taking up to 40 secs to execute and I was wondering how to make it faster...

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

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

Thank you :)

how does your query perform when you remove those 2 left joins? — Leo
what is the point of the left joins? you're not referring to them anywhere — FuzzyTree
he's using the more and more_valid from the left joins as results of the select clause — Leo
is it the v column bit? Or there are different results for the R_l table? — Hector Sanchez
@Leo, it performs really well without the two left join (0.04secs).. but I really need these.. FuzzyTree, more and more_valid are coming from these two left join — cloud1250000

spencer7593 spencer7593 · Accepted Answer · 2014-09-22T22:09:59

For one thing, I'd replace the two inline views with a single inline view, return more and more_valid from the same inline view by moving the conditions from the WHERE clause into expressions in the SELECT list), and eliminating the more_link view.

I'd modify the stm inline view to be something like this:

       ( SELECT q.r_id
              , SUM(q.v=1) AS `more`
              , SUM(q.v=0) AS `more_valid`
           FROM `R_l` q
          WHERE q.type = 'stream'
          GROUP BY q.r_id
       ) stm

I'd also provide a covering index to optimize that inline view query, for example:

    CREATE INDEX R_1_IX1 ON R_1 (type, r_id, v)

With the equality predicate on the leading column type, MySQL may be able to optimize the GROUP BY operation using the index (avoiding a "using filesort" operation). We'd prefer to see "Using index" in the extra column of the explain.

Unfortunately, the derived table will not be indexed. But at least with this change, you're only materializing one inline view, avoids an extra join operation.

That is, I'd replace these two lines:

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`

With this:

  LEFT
  JOIN ( SELECT q.r_id
              , SUM(q.v=1) AS `more`
              , SUM(q.v=0) AS `more_valid`
           FROM `R_l` q
          WHERE q.type = 'stream'
          GROUP BY q.r_id
       ) stm
    ON stm.r_id =  r.id

Optimise sql query

1 Answers