Geek Answers Handbook

Optimize MySQL query with SUM, date range and group by

I have the following tables:

CREATE TABLE IF NOT EXISTS stats (
    date date NOT NULL DEFAULT '0000-00-00',
    cid int(8) NOT NULL DEFAULT '0',
    v bigint(15) NOT NULL DEFAULT '0',
    c bigint(15) NOT NULL DEFAULT '0',
    a bigint(15) NOT NULL DEFAULT '0',
PRIMARY KEY (date,cid),
KEY date (date),
KEY cid (cid),
KEY date_cid_vca (date,cid,v,c,a)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

This table has 30,842,712 rows

and

CREATE TABLE IF NOT EXISTS camp (
id int(8) NOT NULL AUTO_INCREMENT,
name varchar(80) NOT NULL DEFAULT '',
PRIMARY KEY (id,name)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

In this table 1985 rows

I have the following query:

SELECT
    c.id,
    c.name,
    SUM(s.v) AS sumv,
    SUM(s.c) AS sumc,
    GREATEST(((SUM(s.c)/SUM(s.v))*100.00), 0.00) AS cratio,
    SUM(s.a) AS suma,
    GREATEST(((SUM(s.a)/SUM(s.c))*100.00), 0.00) AS aratio
FROM
    stats s, camp c
WHERE
    s.date >= '2012-02-01' AND
    s.date <= '2012-02-29' AND
    c.id=s.cid
GROUP BY s.cid;

EXPLAIN shows:

+----+-------------+-------+-------+-------------------------------+--------------+---------+---------------------+---------+-----------------------------------------------------------+
| id | select_type | table | type  | possible_keys                 | key          | key_len | ref                 | rows    | Extra                                                     |
+----+-------------+-------+-------+-------------------------------+--------------+---------+---------------------+---------+-----------------------------------------------------------+
|  1 | SIMPLE      | s     | range | PRIMARY,date,cid,date_cid_vca | date_cid_vca | 3       | NULL                | 1010265 | Using where; Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | c     | ref   | PRIMARY                       | PRIMARY      | 4       | db.s.cid            |       1 | Using index                                               |
+----+-------------+-------+-------+-------------------------------+--------------+---------+---------------------+---------+-----------------------------------------------------------+

The problem is that the query takes about 50 seconds, even if it uses indexes. Is there any other way to optimize the query?

Thank!

+5
source share
3 answers

. , 1 stats . , ( ) 1 , , , . , ( camp stats, ), , .

Edit

1 + , , , - :

SELECT c.*, a.* FROM
(SELECT
    SUM(s.v) AS sumv,
    SUM(s.c) AS sumc,
    GREATEST(((SUM(s.c)/SUM(s.v))*100.00), 0.00) AS cratio,
    SUM(s.a) AS suma,
    GREATEST(((SUM(s.a)/SUM(s.c))*100.00), 0.00) AS aratio,
    s.cid
FROM
    stats s
WHERE
    s.date >= '2012-02-01'
   AND s.date <= '2012-02-29'
GROUP BY s.cid) a
JOIN
  camp c
  ON c.id = a.cid

.

+4

SELECT
    c.id,
    c.name,
    SUM(s.v) AS sumv,
    SUM(s.c) AS sumc,
    GREATEST(((SUM(s.c)/SUM(s.v))*100.00), 0.00) AS cratio,
    SUM(s.a) AS suma,
    GREATEST(((SUM(s.a)/SUM(s.c))*100.00), 0.00) AS aratio
FROM
    camp c
INNER JOIN
    stats s
ON
    s.cid = c.id
    AND s.date BETWEEN '2012-02-01' AND '2012-02-29'

GROUP BY c.id;

date_cid_vca, . PK cid, , 100% , ,

+1

You can create an internal join to your C table, and use date conditions in the join, this should reduce the time of your query.

You might be able to do more optimizations, but this is the first first I could see.

0
source

More articles:


All Articles