Speed up SUM and COUNT
Moderator: General Moderators
Speed up SUM and COUNT
Is there any way to speed up SUM and COUNT queries for large data sets.
Re: Speed up SUM and COUNT
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.
Re: Speed up SUM and COUNT
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
Other than that, post your specific query and EXPLAIN results on it
Re: Speed up SUM and COUNT
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?
I need to INDEX and MySQL DATE field. Is there anything special you need to do?
Re: Speed up SUM and COUNT
Is it possible to index a date field?
Re: Speed up SUM and COUNT
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
After reading that I still don't see how to effectively INDEX a DATE column?
Re: Speed up SUM and COUNT
did you try adding an index to it..?
Re: Speed up SUM and COUNT
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.
Re: Speed up SUM and COUNT
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
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.
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
We understand what your problem is. The best explanation we can come up with is what ~pytrin said:
Which, if I may digress, is why I don't use phpMyAdmin.pytrin wrote:Adding an index should not effect query results, only performance. something else must have changed in the schema or queries
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
Re: Speed up SUM and COUNT
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.
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
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.
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.