Counting records of a large table based on date format

For reference, this is my current table:

`impression` (
  `impressionid` bigint(19) unsigned NOT NULL AUTO_INCREMENT,
  `creationdate` datetime NOT NULL,
  `ip` int(4) unsigned DEFAULT NULL,
  `canvas2d` tinyint(1) DEFAULT '0',
  `canvas3d` tinyint(1) DEFAULT '0',
  `websockets` tinyint(1) DEFAULT '0',
  `useragentid` int(10) unsigned NOT NULL,
  PRIMARY KEY (`impressionid`),
  UNIQUE KEY `impressionsid_UNIQUE` (`impressionid`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=447267 ;

It keeps a record of all the impressions on a certain page. After one day of running, it has gathered 447266 views. Those are a lot of records.

Now I want the amount of visitors per minute. I can easily get them like this:

SELECT COUNT( impressionid ) AS visits, DATE_FORMAT( creationdate,  '%m-%d %H%i' ) AS DATE
FROM  `impression` 
GROUP BY DATE

This query takes a long time, of course. Right now around 56 seconds.

So I'm wondering what to do next. Do I:

• Create an index on creationdate (I don't know if that'll help since I'm using a function to alter this data by which to group)

• Create new fields that stores hours and minutes separately.

The last one would cause there to be duplicate data, and I hate that. But maybe it's the only way in this case?

Or should I go about it in s开发者_C百科ome different way?

If you run this query often, you could denormaize the calculated value into a separate column (perhaps by a trigger on insert/update) then grouping by that.

Your idea of hours and minutes is a good one too, since it lets you group a few different ways other than just minutes. It's still denormalization, but it's more versatile.

Denormalization is fine, as long as it's justified and understood.