CODEWITHSUNDEEP
mysqlquery-optimization
k102
k102

Reputation: 8079

Mysql query optimisation - sum() function

I've created the following table:

CREATE TABLE `clicks_summ` (
    `dt` INT(7) UNSIGNED NOT NULL,
    `banner` SMALLINT(6) UNSIGNED NOT NULL,
    `client` SMALLINT(6) UNSIGNED NOT NULL,
    `channel` SMALLINT(6) UNSIGNED NOT NULL,
    `cnt` INT(11) UNSIGNED NOT NULL,
    `lpid` INT(11) NULL DEFAULT NULL,
    UNIQUE INDEX `dt` (`dt`, `banner`, `client`, `channel`, `lpid`),
    INDEX `banner` (`banner`),
    INDEX `channel` (`channel`),
    INDEX `client` (`client`),
    INDEX `lpid` (`lpid`),
    INDEX `cnt` (`cnt`)
)
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB;

and i am using following query to fetch rows/records from this table:

 select client, sum(cnt) cnt
 from clicks_summ cs
 group by client;

and it's awful! It takes about a second to perform this query. EXPLAIN shows me Explain SQL Statement result

So, the question is: how I can speed up this query? I've tried indexing this table on different fields without any reasonable success. Now there are 331 036 rows in this table, I guess, is not so big.

Upvotes: 0

Views: 154

Answers (2)

Val
Val

Reputation: 413

one cool thing

if you always have 5 col in your where clause when a grouped index of 5 col will outperform 5 individual indexes ;)

Upvotes: 0

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

Try craete INDEX client_cnt(client, cnt). Another way to make query faster is upgrade your hardware:)

Upvotes: 2

Related Questions