CODEWITHSUNDEEP
mysql
Steve Payne
Steve Payne

Reputation: 631

Working with millions of MySQL rows

I am working on a way to record click times for each user on my website.

I have currently 600,000+ records when trying to think of a way to go about this.

CREATE TABLE IF NOT EXISTS `clicktime` (
  `id` int(5) NOT NULL AUTO_INCREMENT,
  `page` int(11) DEFAULT NULL,
  `user` varchar(20) DEFAULT NULL,
  `time` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=686277 ;

I will have to do ten of these searches per page. My blog shows a snippet of ten pages at once.

SELECT time 
FROM clicktime 
WHERE `page` = '112'
  AND `user` = 'admin' 
ORDER BY `id` ASC LIMIT 1

The call that looks like it's getting me, is the WHERE page = '112'

How can I make this work faster, it is taking up to 3 seconds to pull each call?

Upvotes: 0

Views: 137

Answers (1)

Nanne
Nanne

Reputation: 64399

Though there are multiple things that could be better here (the time being a bigint for instance), the thing that will help you on short term is just to add an index on your user field.

Upvotes: 2

Related Questions