Alvin Alvin - 2 months ago 9
MySQL Question

Create month and year column to speed up timestamp query

I have a big log table in mariadb/mysql:

CREATE TABLE `logs` (
`id` CHAR(36) NOT NULL,
`user` CHAR(4) NOT, NULL,
`dateCreated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`dateUpdated` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB


I am trying to query logs based on user and date created by month and year:

select * from logs where month(dateCreated) = '9' and year(dateCreated) = '2016' and user = '1234'


Question:
Should I created two columns called month and year, and index the month, year, and user to speed up the query?

Answer

You are better off just restructuring your query's criteria to better take advantage of a possible index on the field:

WHERE dateCreated >= '2016-09-01 00:00:00' 
   AND dateCreated < '2016-10-01 00:00:00' 
   AND user = '1234'
Comments