It's running on a table that has 36 Million records. I'd like to it be faster.
I have two indexes setup, one on stat_account and one on stat_created.
So first of all, is there something I should do to make the query faster? Like maybe COUNT(*) isn't a good idea, or there something better than BETWEEN?
And secondly, should I change my indexes? Make a combo index that includes account and created?
Thank you for all your responses! I have a lot of good information to go off of.
To clarify a few things:
We never do deletes.
We insert about 1000/records per minute during peak times and average about 100/minute over all.
We're considering purging old data (might be a good separate question on the most efficient way to delete records older than x days on a regular basis).
Also, if I transform the dates to integers for existing and new incoming records, what would be the best way to do: '2012-11-01 12:59:02'
I'm assuming since I only want month granularity I can just use:
201211
And put an index on that column? That seems to me like it would probably lead to a big performance boost.
@MrBullwinkle-- you said "I think the engine can optimize the repeat calls if you use @parameters rather than a string literal". I'm not sure what @parameters are?
To clarify a few things:
We never do deletes.
We insert about 1000/records per minute during peak times and average about 100/minute over all.
We're considering purging old data (might be a good separate question on the most efficient way to delete records older than x days on a regular basis).
A few questions...
To type cast, is this the best way:
Open in new window
Also, if I transform the dates to integers for existing and new incoming records, what would be the best way to do: '2012-11-01 12:59:02'
I'm assuming since I only want month granularity I can just use:
201211
And put an index on that column? That seems to me like it would probably lead to a big performance boost.
@MrBullwinkle-- you said "I think the engine can optimize the repeat calls if you use @parameters rather than a string literal". I'm not sure what @parameters are?
Thanks again for all your help!