CODEWITHSUNDEEP
mysqldateindexing
bestprogrammerintheworld
bestprogrammerintheworld

Reputation: 5520

Should i put an index on my DATE-field?

I'm designing a whole new database. There would be a lot of queries going on each day. It's actually a very,very simple time-accounting system but I still want to be as fast as possible. I'm building it myself because I can, and I could save som money and at the same time get it exactly how I want, just to get a grasp of time that I'm consuming in different projects. 

CREATE TABLE IF NOT EXISTS `activity` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `activity_date` date NOT NULL,
  `minutes` int(11) NOT NULL COMMENT 'time for this activity (in minutes)
  `week_nr` tinyint(2) unsigned NOT NULL COMMENT 'store weeknr for faster selection',
  `year_nr` smallint(4) unsigned NOT NULL COMMENT 'store yearnr for fast selection',
  `month_nr` tinyint(2) unsigned NOT NULL COMMENT 'store monthnr for faster selection',
  PRIMARY KEY (`id`),
  KEY `week_nr` (`week_nr`),
  KEY `year_nr` (`year_nr`),
  KEY `month_nr`, (`month_nr`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci COMMENT='Table for storing activities' AUTO_INCREMENT=1 ;

When user is saving an activity in the table above, then my thought is to update year_nr, week_nr and month_nr for the typed-in activity_date so the system easily could get a report for activities between, lets say week 5 and 10, or between month 5 (May) and month 10 (October).

Is this a good idea or is better to just put an index on the date-field? My prediction is that would be a lot more select-queries going on, then it would be updates.

Upvotes: 1

Views: 103

Answers (2)

Markus Winand
Markus Winand

Reputation: 8746

The answer is quite easy considering the following tip:

Write queries for continuous periods as explicit range condition.

Example from 2013 till 2014:

WHERE activity_date >= '2013-01-01' and activity_date < '2015-01-01'

All you need to do is to calculate the boundary dates and then use this pattern. This pattern can use a straight index on activity_date — no additional columns/indexes needed. It works as long as you need to query a continuous period of time (as opposed to "every monday" which are scattered periods of time).

Don't apply any functions to indexed columns. Don't YEAR(activity_date) that's the anti-pattern to avoid :)

Reference: http://use-the-index-luke.com/sql/where-clause/obfuscation/dates

Upvotes: 1

Aman Aggarwal
Aman Aggarwal

Reputation: 18479

I think its a bad idea. You should build a index on date field rather..

Upvotes: 0

Related Questions