Help me to optimize this query

Please suggest indexes to optimize below query. I couldn't allowed to rewrite the query but create indexes:

SELECT 
    `ADV`.`inds` as `c0`, 
    sum(`ADVpost`.`clk`) as `m0`
FROM 
    (SELECT *
     FROM advts
     WHERE comp_id = 
        (SELECT comp_id 
         FROM comp 
         WHERE name = 'abc'))  as `ADV`, 
        (SELECT dt_id,
                comp_id,
                b_id,
                ad_id,
                clk,
                resp
         FROM advts_post
         WHERE comp_id = 
                 (SELECT comp_id 
                  FROM comp 
                  WHERE name = 'abc')) as `ADVpost`
WHERE 
    `ADVpost`.`ad_id` = `ADV`.`ad_id`
GROUP BY 
    `ADV`.`inds`
ORDER BY 
    ISNULL(`ADV`.`inds`), `ADV`.`inds` ASC 

The explain for the query is as:

select_type table       type    possible_keys   Extra
PRIMARY     <derived2>  ALL     null            Using temporary; Using filesort
PRIMARY     <derived4>  ALL     null            Using where; Using join buffer
DERIVED     ADVpost     ALL     null            Using where
SUBQUERY    comp        ALL     null            Using where
DERIVED     advts       ALL     null            Using where
SUBQUERY    comp        ALL     n开发者_如何学编程ull            Using where

Existing indexes are as follows:

ADVpost > PRIMARY KEY (`dt_id`,`comp_id`,`b_id`,`ad_id`)

comp    > PRIMARY KEY (`comp_id`)

advts   > PRIMARY KEY (`ad_id`)

Thanks in advance.

---

Ok, maybe I am not an expert with MySQL optimization, but:

• if it is possible and reasonable, try to avoid subselects where possible (instead it may be better to make separate query and then pass the retrieved ID, like comp_id, to the containing query),
• put index on comp.name,
• put index on advts_post.comp_id (single one),
• put index on advts_post.ad_id (single one),

Maybe it is rather simple, but should help at least slightly make it faster. Tell us about the results.

---

That query is a dogs dinner - whoever wrote it should be severely punished, and the person who said you can't rewrite it but must make it run faster should be shot.

Loose the sub-selects!

MySQL does not do push-predicates very well (at all?).

Use proper joins instead and state implied joins:

SELECT ap.inds, SUM(ap.clk)
FROM advts_post AS ap
, comp AS co
, advts ad
WHERE ap.comp_id = co.comp_id
AND ad.comp_id = co.comp_id
AND ap.comp_id = ad.comp_id
AND co.name='abc'
GROUP BY ap.inds
ORDER BY ISNULL(ap.inds), ap.inds ASC