Re: Massive SELECTs for a hit counter

[Benjamin Choi]

CREATE TABLE `hits` (
`hit_id` int(10) unsigned NOT NULL auto_increment,
`user_id` int(10) unsigned NOT NULL default '0',
`timestamp` int(10) unsigned NOT NULL default '0',
`ip` varchar(15) NOT NULL default '',
`referrer` varchar(255) NOT NULL default '0',
PRIMARY KEY (`hit_id`),
KEY `user_id` (`user_id`)
) TYPE=InnoDB AUTO_INCREMENT=4000001;

As you can see, there are about four million rows in the table.

mysql>EXPLAIN SELECT COUNT(*) FROM hits WHERE user_id = 1;
->table type possible_keys key key_len ref rows Extra
->hits ref user_id user_id 4 const 2000104 Using where; Using index

mysql>EXPLAIN SELECT COUNT(user_id) FROM hits WHERE user_id = 1;
-> table type possible_keys key key_len ref rows Extra
->hits ref user_id user_id 4 const 2000104 Using where; Using index

Either SELECT takes about 3.7-3.8 seconds. With eight million rows, it takes about eight or nine seconds. I'm using an Athlon XP 2100+ on 512MB of RAM. The same goes for using a MyISAM table engine.