Page 1 of 1

Speed up SUM and COUNT

Posted: Mon Feb 08, 2010 12:02 pm
by icesolid
Is there any way to speed up SUM and COUNT queries for large data sets.

Re: Speed up SUM and COUNT

Posted: Mon Feb 08, 2010 12:26 pm
by pickle
If you create an index on the column, that should certainly speed up COUNT(). Not sure if SUM() would be affected.

Re: Speed up SUM and COUNT

Posted: Mon Feb 08, 2010 1:21 pm
by Eran
Both an index on the aggregated column and on the grouped-by column could help. Also, you could suppres the order a group-by introduces by adding "ORDER BY NULL"

Other than that, post your specific query and EXPLAIN results on it

Re: Speed up SUM and COUNT

Posted: Mon Feb 08, 2010 1:30 pm
by icesolid
The index thing worked but then my date results got screwed up elsewhere on my site.

I need to INDEX and MySQL DATE field. Is there anything special you need to do?

Re: Speed up SUM and COUNT

Posted: Mon Feb 08, 2010 2:39 pm
by icesolid
Is it possible to index a date field?

Re: Speed up SUM and COUNT

Posted: Mon Feb 08, 2010 2:44 pm
by Eran
yes. you can index any column type, with certain provisions - http://dev.mysql.com/doc/refman/5.0/en/indexes.html

Re: Speed up SUM and COUNT

Posted: Mon Feb 08, 2010 2:48 pm
by icesolid
After reading that I still don't see how to effectively INDEX a DATE column?

Re: Speed up SUM and COUNT

Posted: Mon Feb 08, 2010 2:51 pm
by Eran
did you try adding an index to it..?

Re: Speed up SUM and COUNT

Posted: Mon Feb 08, 2010 3:08 pm
by icesolid
I clicked INDEX in phpMyAdmin next to the column and now my queries that use the column to search do not produce any results if I do a BETWEEN in my WHERE clause?

Re: Speed up SUM and COUNT

Posted: Mon Feb 08, 2010 3:11 pm
by Eran
Adding an index should not effect query results, only performance. something else must have changed in the schema or queries

Re: Speed up SUM and COUNT

Posted: Mon Feb 08, 2010 3:12 pm
by icesolid
I clicked INDEX in phpMyAdmin next to the column and now my queries that use the column to search do not produce any results if I do a BETWEEN in my WHERE clause?

If I just do date_completed = '2010-02-08' then it works BUT date_completed BETWEEN '2010-02-08' AND '2010-02-08' it does not work.

Re: Speed up SUM and COUNT

Posted: Mon Feb 08, 2010 3:20 pm
by pickle
We understand what your problem is. The best explanation we can come up with is what ~pytrin said:
pytrin wrote:Adding an index should not effect query results, only performance. something else must have changed in the schema or queries
Which, if I may digress, is why I don't use phpMyAdmin.

Re: Speed up SUM and COUNT

Posted: Mon Feb 08, 2010 3:22 pm
by icesolid
That was just a typo in my last post. The first date in my code was 2010-02-05

Might I add, this query works when I unindex the field. Once I click index it produces a 0. When I unindex it it produces 12 which is the correct number of results.

Re: Speed up SUM and COUNT

Posted: Tue Feb 09, 2010 7:40 am
by icesolid
?

Re: Speed up SUM and COUNT

Posted: Sun Feb 14, 2010 10:15 am
by Darhazer
It's best to post your query and the result of EXPLAIN statement, as well as db scheme.

Addionally, if your application needs to perform a lot of agragate functions, and your tables have a lot of data in them, a column based storage engine can give you good increase in the perfromance. But before going to such levels of optimizations, you have to be sure that the queries are optimized, because nothing can help you against not optimized queries and wrong DB design.