MySQL Forums
Forum List  »  Performance

Re: Massive SELECTs for a hit counter
Posted by: Benjamin Choi
Date: December 14, 2004 11:45AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
3422
December 13, 2004 04:18PM
Re: Massive SELECTs for a hit counter
2400
December 14, 2004 11:45AM
2183
December 14, 2004 08:06PM
2254
December 14, 2004 12:55PM
2126
December 14, 2004 11:35PM
3623
December 16, 2004 01:45AM
2368
December 15, 2004 01:19PM
2322
December 15, 2004 01:39PM
2227
December 15, 2004 01:53PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.