top items in mysql

snapbackz

I have a database I store stats in for views.. but I am not sure how to find the top 5 or so viewed items. Here is the table structure:

CREATE TABLE `item_views` (
  `id` int(8) NOT NULL auto_increment,
  `myid` int(8) NOT NULL,
  `ip` varchar(14) NOT NULL,
  `date` date NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `item_id` (`myid`,`ip`)
)

It has quite a few rows for each myid. Did I set this up wrong to ge the top items? I'm guessing I need to group the myid's but I would like to know the proper or most efficient way to store stats, since i need the top items frequently.

laserlight

You need to be more clear on what are the entities and relationships involved. From your table schema, I can guess that an item view has an id, but what is myid? If it a foreign key, and if so, to what table? Then, what is the relationship between item_views and this other table? (An items table, I presume?)

I could guess that you want a query like:

SELECT myid, COUNT(myid) AS myid_count
    FROM item_views
    GROUP BY myid
    ORDER BY myid_count DESC
    LIMIT 5

snapbackz

Yes, myid is the foreign key to the items table. Sorry for not being clear.

Your query is exactly what I was looking for, thank you so much! One problem though, it's painfully slow. The table has 18+million rows. When I run this from a php script it times out. I can only run it from putty. This is why I asked if I was storing stats correctly? Any ideas to get the query to run quicker?

Thank you for your help sofar! I really appreciate it. 🙂

laserlight

You could have the items table keep track of the number of views for each item as well. Unfortunately, there is a chance that this number can become out of sync with the actual number based on item_views, so every once in awhile you would have to resynchronise with a query like what I used.

snapbackz

hmm ok. There has got to be a better method.

You have been very helpful, thank you laserlight. 🙂