Re: Optimizing date query on large table.

[Rick James]

Your datasize is much bigger than RAM, correct? Even your index size is bigger than can be cached, correct?

With 500M records, extra columns, extra table, extra indexes -- all of these will probably make things worse, not better.

I recommend having a field with the datatype DATE and index it; no other indexes relating to the field. Then...

* This will be quite efficient:
SELECT * FROM MyTable WHERE mDate BETWEEN '1795-03-01' AND '1795-03-31'

* Similarly, but my preference:
SELECT * FROM MyTable WHERE mDate >= '1795-03-01'
AND mDate < DATE_ADD('1795-03-01', INTERVAL 1 MONTH)
(That way, I don't have to figure out when a month ends.)

* This will do a table scan:
SELECT * FROM MyTable WHERE MONTH(mDate) = 3;
But -- if you had a separate month column it might either do a table scan or spend an awful amount of time bouncing between the index and the table.

* I am avoiding Chad's extra table because that is even worse for bouncing back and forth. Note, with 500M rows, you are probably I/O bound, and some of this bouncing would be random disk hits.

* Having just the one index (one for date anyway) means that there are not lots of indexes competing for cache space (key_buffer or innodb_buffer_pool).

Depending on what other columns you have, you may need
INDEX(some_other_field, mDate)
You are unlikely to have any use for
INDEX(mDate, some_other_field)
unless you are doing "WHERE mDATE = '...' AND some_other_field BETWEEN...".