CODEWITHSUNDEEP
mysql
mybigman
mybigman

Reputation: 339

Simple Query Slow

Is there anyway to get better performance out of this.

SELECT fdyofmth, SUM(total_restored) AS total_restored
FROM sum_restored
WHERE fdyofmth BETWEEN '2010-11-01' AND '2011-11-01'
GROUP BY fdyofmth
ORDER BY fdyofmth DESC

The result takes 3 secs.

+------------+----------------+
| fdyofmth   | total_restored |
+------------+----------------+
| 2011-11-01 |          39793 |
| 2011-10-01 |          52367 |
| 2011-09-01 |          52141 |
| 2011-08-01 |          56515 |
| 2011-07-01 |          54668 |
| 2011-06-01 |          54874 |
| 2011-05-01 |          60025 |
| 2011-04-01 |          60005 |
| 2011-03-01 |          63856 |
| 2011-02-01 |          64472 |
| 2011-01-01 |          58943 |
| 2010-12-01 |          57005 |
| 2010-11-01 |          55731 |
+------------+----------------+

EXPLAIN result -

+----+-------------+--------------+------+---------------+------+---------+------+--------+----------------------------------------------+
| id | select_type | table        | type | possible_keys | key  | key_len | ref  | rows   | Extra                                        |
+----+-------------+--------------+------+---------------+------+---------+------+--------+----------------------------------------------+
|  1 | SIMPLE      | sum_restored | ALL  | NULL          | NULL | NULL    | NULL | 752660 | Using where; Using temporary; Using filesort |
+----+-------------+--------------+------+---------------+------+---------+------+--------+----------------------------------------------+
1 row in set

I have tried putting a KEY of fdyofmth which actually made it twice as slow.

Any help would be much appreciated.

Thank you.

Upvotes: 2

Views: 182

Answers (4)

James.Xu
James.Xu

Reputation: 8295

I think it is slow, because you have so much data, and your sql has to go through all the data(not pick one from the whole data set), so index can not help here.

The option i propose is use parallelism:

  1. add the index as suggested by @newtover
  2. use 12 threads to retrieve the results for each month.

Or even simpler:

just pre-compute the result into another table, when query, query from that table.

Upvotes: 1

newtover
newtover

Reputation: 32094

According to your EXPLAIN results, the query doesn't use any indexes. You need to set up an index on fdyofmth:

ALTER TABLE sum_restored ADD INDEX fdyofmth_index (fdyofmth);

Or another suggestion =):

SELECT
    SUM (IF(fdyofmth = '2010-11-01', total_restored, NULL)) as `2010-11-01`,
    SUM (IF(fdyofmth = '2010-12-01', total_restored, NULL)) as `2010-12-01`,
    SUM (IF(fdyofmth = '2011-01-01', total_restored, NULL)) as `2011-01-01`,
    SUM (IF(fdyofmth = '2011-02-01', total_restored, NULL)) as `2011-02-01`,
    SUM (IF(fdyofmth = '2011-03-01', total_restored, NULL)) as `2011-03-01`,
    SUM (IF(fdyofmth = '2011-04-01', total_restored, NULL)) as `2011-04-01`,
    SUM (IF(fdyofmth = '2011-05-01', total_restored, NULL)) as `2011-05-01`,
    SUM (IF(fdyofmth = '2011-06-01', total_restored, NULL)) as `2011-06-01`,
    SUM (IF(fdyofmth = '2011-07-01', total_restored, NULL)) as `2011-07-01`,
    SUM (IF(fdyofmth = '2011-08-01', total_restored, NULL)) as `2011-08-01`,
    SUM (IF(fdyofmth = '2011-09-01', total_restored, NULL)) as `2011-09-01`,
    SUM (IF(fdyofmth = '2011-10-01', total_restored, NULL)) as `2011-10-01`,
    SUM (IF(fdyofmth = '2011-11-01', total_restored, NULL)) as `2011-11-01`
FROM sum_restored;

Upvotes: 0

Antti Rytsölä
Antti Rytsölä

Reputation: 1545

You should resolve this outside query changes.

  • Create another table to hold the ready calculated summaries for each past month
  • cut down the size of the table a lot. Another table for every year or every month or row changes.

Upvotes: 0

Sudhir Bastakoti
Sudhir Bastakoti

Reputation: 100175

Try this: If the dates are not the same, then

SELECT fdyofmth, SUM(total_restored) AS total_restored
FROM sum_restored
WHERE fdyofmth >= '2010-11-01' AND fdyofmth <= '2011-12-01'
GROUP BY fdyofmth
ORDER BY fdyofmth DESC

Hope it helps

Upvotes: 0

Related Questions