CODEWITHSUNDEEP
mysqlsql
Jelle De Loecker
Jelle De Loecker

Reputation: 21985

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:

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 some different way?

Upvotes: 1

Views: 158

Answers (1)

Bohemian
Bohemian

Reputation: 425238

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.

Upvotes: 2

Related Questions