MySQL Optimisation Tip #2: Store counters in seperate tables
Store counters in seperate tables
A common scenario, you have a "pages" table and you want to store a hit counter in that table so you will know which pages are the most popular, you would have something like:
CREATE TABLE `pages` (
`pageId` int(10) unsigned NOT NULL auto_increment,
`pageName` varchar(255) NOT NULL,
`pageHTML` text NOT NULL,
`pageHitCounter` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`pageId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Now when you want to update the hit counter you will do something like:
UPDATES pages set pageHitCounter = pageHitCounter+1 WHERE pageId = 12
This is fine for normal database schemas... but if you are using the MySQL Query Cache feature (which you should), every time you execute the update you will invalidate the cache making the whole cache feature just about useless.
Instead what you can do is store the hitCounter for each page in a seperate table.
So add something like
CREATE TABLE `pageHitCounter` (
`pageId` int(10) unsigned NOT NULL auto_increment,
`pageHitCounter` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`pageId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
and update that instead. That was a fairly contrived example but you get the idea - be careful not to invalidate the cache.
Comments [3]
Peter Coppinger aka Topper is a neurotic web monster who spends most of his chaotic life developing ColdFusion web applications when not drinking himself into a stupor and scheming his plans for world dominance.
