Mysql Index for date fields

Question

I have a datetime column and I have to filter by date and group it by day or month. This is a million rows table.

|IssueDate          |
--------------------
|2015-11-24 16:46:00|

This is the first choice without any index because I always must use a date function:

select * from myTable where DATE(IssueDate)='2015-11-24'
select * from myTable group by DATE(IssueDate)
select * from myTable group by YEAR(IssueDate), MONTH(IssueDate)

This is the second choice, to separate time and date in the table:

|IssueDate |Issuetime|
----------------------
|2015-11-24|16:46:00 |

Here I can index the date field:

select * from myTable where IssueDate='2015-11-24'
select * from myTable group by IssueDate
select * from myTable group by YEAR(IssueDate), MONTH(IssueDate)

Another try with redundancy fest:

|IssueDate |Issuetime|IssueMonth|IssueYear|
-------------------------------------------
|2015-11-24|16:46:00 |11        |2015     |

Here I can index all my fields:

select * from myTable where IssueDate='2015-11-24'
select * from myTable group by IssueDate
select * from myTable group by IssueYear, IssueMonth

What is the way to index this for the best performance?

Gordon Linoff · Accepted Answer · 2015-11-24 15:57:51Z

1

Part of your issue is writing queries so they can use indexes.

Instead of writing:

select *
from myTable
where DATE(IssueDate) = '2015-11-24';

Try:

select *
from myTable
where IssueDate >= '2015-11-24' AND IssueDate < '2015-11-25';

Your other two examples use group by. I find that it is quite hard to get MySQL to use an index for aggregation in a query.

answered Nov 24, 2015 at 15:57

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Vatev Over a year ago

And by "quite hard" you can read: it only works for MIN and MAX with specific JOINs and WHERE...

Tobia Over a year ago

If I need for find (and not group) by month and year I think that should be the same as you suggested for date: where IssueDate >= '2015-11-01' AND IssueDate < '2015-11-30';

Vatev Over a year ago

@Tobia Yes, a single index on a datetime will be sufficient for filtering.

Tobia Over a year ago

Are there any advantages to divide date part from time part (in these cases or any other scenario)? For example if I need only a equal filter (date='2015-11-24' ) instead of between filter (IssueDate >= '2015-11-24' AND IssueDate < '2015-11-25')

Rick James Over a year ago

I prefer the pattern: d >= '2015-11-24' AND d < '2015-11-24' + INTERVAL 1 DAY. It avoids all sort of problems, such as leap year. (Note that @Tobia solution accidentally failed to include the last day of Nov.)

|

Collectives™ on Stack Overflow

Mysql Index for date fields

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related