1

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.

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.

  • thanks - now i need to learn a bit more about triggers; eg do I need to recreate them every time mysql starts? I'll research a bit, thanks for your help! – user16081 Dec 23 '14 at 18:19
  • No, triggers are stored within the database. They are created once and run automatically. – ForguesR Dec 23 '14 at 18:31
  • perfect, so once I create the new table and write the triggers for each existing table, I won't need any new code to put data into the new table. This is my plan now, thank you! – user16081 Dec 23 '14 at 19:27
1

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.

  • thanks Felipe - with triggers, do you know if there is a way to automatically limit the inserts to (a) unique combinations and (b) 25 rows per user_id? – user16081 Dec 23 '14 at 1:24
  • I would do it manually for a) and b). Dont know if there is a way to do this automatically. As for a) count(*) how many items a user_id have, if is less than 25 insert it.. else, delete the oldest and insert it. As for a) you can rely on the structure of the table, using a unique constraint on these two columns. Instead of counting and then inseting, you can always insert and ignore the errors if the value is already there. try insert into t... except when dup_val_on_unique null; end; – Felipe Moreno Dec 23 '14 at 2:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.