Speed up SUM and COUNT

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
icesolid
Forum Regular
Posts: 502
Joined: Mon May 06, 2002 9:36 pm
Location: Buffalo, NY

Speed up SUM and COUNT

Post by icesolid »

Is there any way to speed up SUM and COUNT queries for large data sets.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: Speed up SUM and COUNT

Post by pickle »

If you create an index on the column, that should certainly speed up COUNT(). Not sure if SUM() would be affected.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Speed up SUM and COUNT

Post 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
icesolid
Forum Regular
Posts: 502
Joined: Mon May 06, 2002 9:36 pm
Location: Buffalo, NY

Re: Speed up SUM and COUNT

Post 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?
icesolid
Forum Regular
Posts: 502
Joined: Mon May 06, 2002 9:36 pm
Location: Buffalo, NY

Re: Speed up SUM and COUNT

Post by icesolid »

Is it possible to index a date field?
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Speed up SUM and COUNT

Post by Eran »

yes. you can index any column type, with certain provisions - http://dev.mysql.com/doc/refman/5.0/en/indexes.html
icesolid
Forum Regular
Posts: 502
Joined: Mon May 06, 2002 9:36 pm
Location: Buffalo, NY

Re: Speed up SUM and COUNT

Post by icesolid »

After reading that I still don't see how to effectively INDEX a DATE column?
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Speed up SUM and COUNT

Post by Eran »

did you try adding an index to it..?
icesolid
Forum Regular
Posts: 502
Joined: Mon May 06, 2002 9:36 pm
Location: Buffalo, NY

Re: Speed up SUM and COUNT

Post 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?
Last edited by icesolid on Mon Feb 08, 2010 3:12 pm, edited 1 time in total.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Speed up SUM and COUNT

Post by Eran »

Adding an index should not effect query results, only performance. something else must have changed in the schema or queries
icesolid
Forum Regular
Posts: 502
Joined: Mon May 06, 2002 9:36 pm
Location: Buffalo, NY

Re: Speed up SUM and COUNT

Post 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.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: Speed up SUM and COUNT

Post 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.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
icesolid
Forum Regular
Posts: 502
Joined: Mon May 06, 2002 9:36 pm
Location: Buffalo, NY

Re: Speed up SUM and COUNT

Post 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.
icesolid
Forum Regular
Posts: 502
Joined: Mon May 06, 2002 9:36 pm
Location: Buffalo, NY

Re: Speed up SUM and COUNT

Post by icesolid »

?
User avatar
Darhazer
DevNet Resident
Posts: 1011
Joined: Thu May 14, 2009 3:00 pm
Location: HellCity, Bulgaria

Re: Speed up SUM and COUNT

Post 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.
Post Reply