Is there a more efficient way to set up this table

Question

I'm looking to add a new feature in our production site that will show users 25 items they most recently viewed. I already record every view into various tables based on the item type, but querying on the union of these tables can't be efficient, because those tables have (in some cases) millions of rows, and for the new table, I'm only concerned with the user's 25 most recent views.

My initial thought was to make a new table and write additional code to add records to (and delete from) this table with the following conditions:

1) each combination of user_id and item_id should be unique; ie for a given user_id, any item should appear no more than once
2) to keep the table small / fast, no more than 25 items should be stored for a given user. Once a user_id has 25 items the oldest view would be removed whenever a new view is added for that user_id.

so in order to do this, I need to query if the given combination exists, and then if the given user_id has 25 rows, the user's oldest row would need to be deleted.

I thought there may be a better way to do this and I investigated MySQL 'views' but from what I read it sounds like creating a view based on the existing tables would not be efficient since it would use all the rows but no indexing.

Is there a better way to do this (maybe triggers? or some way within the mysql schema?) or is my best bet "brute force"; to create additional code to manage the new table, on top of the code I already have to record every view?

This is the basic schema for the new table:

| Field                 | Type  
+-----------------------+-----------------  
| customer_id           | int(11)  
| item_id               | int(11)   
| view_date             | datetime  

thank you in advance for any advice or suggestions.

Answer 1

Condition 1 : You can define a composite primary key such as :

CREATE customerItem (
  customer_id INT,
  item_id INT,
  view_date DATETIME,  
  PRIMARY KEY (customer_id, item_id)
) 

You can then use INSERT ... ON DUPLICATE KEY UPDATE such as :

INSERT INTO customerItem (customer_id,item_id,view_date) VALUES (123,999,"2014-12-22")
ON DUPLICATE KEY UPDATE view_date = VALUES(view_date);

Condition 2 : A trigger seems the way to go. Inspired by this answer on SO I wrote this UNTESTED code which might do the job (or, at least, can be a good start) :

CREATE TRIGGER handleMaxRows BEFORE INSERT ON customerItem 
FOR EACH ROW
BEGIN
  IF (SELECT COUNT(*) FROM customerItem WHERE customer_id = NEW.customer_id) = 25 THEN
    DELETE FROM customerItem 
    WHERE customer_id = NEW.customer_id
    ORDER BY view_date 
    LIMIT 1;
  END IF;    
END;

Edit : Forgot DELETE condition inside the trigger.

Answer 2

I think your approach is reasanoble. Because it would show online data (no need for background processing) and would only use resources from active transactions (customer not seeing anything will not need to have their data reprocessed).

I would do it using triggers. You would not have to change your existing code and you could disable/enable this new functionality more easily.

An idea: Try to use a LRU algorithm. Try to see the "importance" of an item. For example, adding an extra column for counting the amount of times an item as viewed. If a user visited an item many times you could not stop showing it on this Top25 List simply because some other 24 random items were searched.