Multiple Queries to a Large MySQL Table

I have a table with columns ID(int), Number(decimal), and Date(int only timestamp). There are millions of rows. There are indexes on ID and Date.

On many of my pages I am querying this four or five times for a list of Numbers in a specified date range (the range being different each query).

Like:

select number,date where date < 111111111 and date >111111100000

I'm querying these sets of data to be placed on several different charts. "Today vs Yesterday", "This Month vs Last Month", "This Year vs Last Year".

Would querying the largest possible result set with the sql statement and then using my programming language to filter down the query via a sorted and spliced array be better than waiting for each of these 0.3 second queries to finish?

Is there something e开发者_Python百科lse that can be done to speed this up?

It depends on the result set and the executing speed of your queries. There is no ultimate answer to this question.

You should benchmark and calculate the results if you really need to speed up things.

But keep in mind that premature optimization should be avoided besides that you'll implement an already implemented logic in your code which can contain bugs, etc. etc.

While it may cause the query to perform quicker you have to ask yourself about the potential impacts to memory if you were to attempt to load in the entire range of records and then aggregating it programatically.

Chances are that the MySQL optimatizations based on index will perform better than anything you could come up with anyway so it sounds like a bad idea.