(My)SQL optimization question

I have an SQL, which takes more than minute to execute, which is too much for me, can someone give advice on optimization.

UPDATE 2 - a little modified sql with increase of speed:

SELECT STRAIGHT_JOIN
kw.`id` ,  kw.`clientid` ,  kw.`day` ,  kw.`campaignid` ,  kw.`campaign` ,  kw.`adgroupid`,  kw.`adgroup` ,  kw.`matchtype` ,  
kw.`firstpagecpc` ,  kw.`keywordtext` ,  kw.`qualityscore` ,  kw.`maxcpc` ,  kw.`avgcpm` ,  kw.`status` ,  kw.`keywordtext` ,  
kw.`matchtype` ,

SUM( kw.`impressions` )                                                  AS impressions,
SUM(  kw.`clicks` )                                                      AS clicks,
SUM(  kw.`impressions` *  kw.`avgposition` ) / SUM(  kw.`impressions` )  AS avgposition, 
SUM(  kw.`cost` )                                                        AS cost,
(SUM(  kw.`clicks` ) / SUM(  kw.`impressions` ) *100)                    AS ctr,
(SUM(  kw.`cost` ) / SUM(  kw.`clicks` ))                                AS avgcpc,
IF(kw.maxcpc = 0,kw.maxcpc,ad.defaultmaxcpc)                             AS maxcpc

FROM  `keywordsreport`                                                   AS kw
JOIN  `adgroupreport`                                                    AS ad 
ON (kw.clientid = ad.clientid AND kw.`day` = ad.day)

WHERE  
kw.`campaignid` = '55347673'
AND kw.`clientid` = '6588826821'
AND kw.`day` BETWEEN '2011-07-01' AND '2011-07-31'
AND (kw.`adid` = '' OR kw.`adid` = '0')
GROUP BY kw.`keywordtext`, kw.`matchtype`

keywordsreport table indexes:

PRIMARY  PRIMARY 235232  id

Unique   UNIQUE  235232  adgroupid
                         day
                         keywordtext
                         matchtype
                         adid
INDEX    INDEX   16802   keywordtext
                          matchtype
                          clientid

CLIENTIDDAY  INDEX   6637 clientid
                          day

adgroupreport table indexes:

PRIMARY          PRIMARY 1328238  id

CLIENTIDD开发者_C百科AY  INDEX   6447     clientid
                                  day

UPDATE 3: Sample query with EXPLAIN:

    i  select_type  table   type    possible_keys                                      key                key_len   ref     rows    Extra
    1   SIMPLE      kw      range   CLIENTID,CLIENTIDDAY,ADIDDAY,CAMPAIGNCLIENTDAY     CAMPAIGNCLIENTDAY  211   NULL    350 Using where; Using temporary; Using filesort
    1   SIMPLE      ad      range   CLIENTIDDAY                                        CLIENTIDDAY        65    NULL    42464   Using where

In each table: keywordsreport AND adgroupreport there could be ~ 1 000 000 records.

I would go one step further... A client can have many campaigns, so your index should be on the smallest criteria first. I would have an index on

( CampaignID, ClientId, Day )

then just for grins to match the anticipated index for optimizing, just adjust the WHERE clause to

WHERE
         kw.campaignid = '23213231232'
      AND kw.clientid = '$clientid'
      AND kw.`day` BETWEEN '2011-07-31' AND '2011-07-01'
      AND ( kw.adid = '' OR kw.adid = '0')

it looks like you need indexes on clientid and or campaignid. tough without more details.

The unique index in kw table can't be used because you have no adgroupid in your where clause. create an index on clientid, day

Simple Query for Optimization,

Employee table It has two column,like id Totalmarks(out of 900) 1 700 2 600 . . . . n .

SELECT * FROM Employee WHERE Totalmarks BETWEEN 300 AND 600

it will take time for execution query few second.

but

ALTER TABLE Totalmarks ADD INDEX ( Totalmarks  );
SELECT * FROM Employee  WHERE Totalmarks BETWEEN 300 AND 600;

Those are the two indexes recommended by online PawSQL tool,

CREATE INDEX PAW_IDX1564931998 ON keywordsreport(campaignid,clientid,keywordtext,matchtype);
CREATE INDEX PAW_IDX1028958902 ON keywordsreport(campaignid,clientid,day);

Looks the first help your to quickly filter the data and avoid sorting; the second one just help your to filter your data quickly. Try them and pick the fast one.