0

Right now I'm using:

MONTH(timestamp) = '7'AND YEAR(timestamp) = '2013'

To select by date but I've been told this is a very inefficient way of going about it, especially with a large amount of data. What is a faster way of obtaining the same results?

Thanks.

cantread
  • 405
  • 1
  • 7
  • 14

3 Answers3

2

Create an index on timestamp and then use:

where timestamp >= '2013-07-01' and timestamp < '2013-08-01'

This will use the index and perform well.

You create the index as:

create index <your table name>_timestamp on <your table name>(timestamp);
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
1

The problem is that it applies a function on every comparison. You can fix this with:

  1. Not calling a function:

    where timestamp between date '2013-07-01' and date '2013-07-31'

  2. Another way: create funcitonal index:

    create index myIndex on myTable(MONTH(timestamp),YEAR(timestamp));

For info: Is it possible to have function-based index in MySQL?

Community
  • 1
  • 1
smnbbrv
  • 23,502
  • 9
  • 78
  • 109
  • MySQL supports neither functional indexes nor the `date` syntax you have suggested. – Gordon Linoff Feb 04 '14 at 16:44
  • https://dev.mysql.com/doc/refman/5.0/en/date-and-time-literals.html - see documentation for literals if you think so. About indices - see link in the answer – smnbbrv Feb 04 '14 at 16:46
  • . . I don't see what index prefixes on character columns have to do with functions such as `month()` and `year()` on a datetime column. MySQL does not support the syntax that you have in the second part of your answer. – Gordon Linoff Feb 04 '14 at 18:47
0

Search for the range of dates, inclusive of 7/1 and exclusive of 8/1.

WHERE timestamp >= '2013-07-01' and timestamp < '2013-08-01'

Mysql helpfully automatically parses the date from YYYY-MM-DD foramt.

PaulProgrammer
  • 16,175
  • 4
  • 39
  • 56