Mysql – more efficient way to set up this table

limitsMySQL

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.

Best Answer

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.

Related Solutions

Mysql – How to index this table (a_level, b_level, item_id)

Since your table only three columns, this setup can be straightforward.

Here is an ideal table layout for items

CREATE TABLE items
(
    item_id int not null auto_increment,
    a_level int not null,
    b_level int not null,
    KEY  ai  (a_level,item_id),
    KEY  bi  (b_level,item_id),
    KEY  abi (a_level,b_level,item_id),
    KEY  bai (b_level,a_level,item_id),
    PRIMARY KEY     (item_id)
);

Here are the realm of possible queries

SELECT * FROM items WHERE id = 100; (uses the PRIMARY KEY)
SELECT * FROM items WHERE a_level = 200; (uses KEY ai)
SELECT * FROM items WHERE b_level = 300; (uses KEY bi)
SELECT * FROM items WHERE a_level = 400 order by b_level (uses KEY abi ordering all b_level values under a_level 400 and orders item_id under each b_level)

YYou may try out other combinations of SELECT...GROUP BY...ORDER BY.... You should also run EXPLAIN on every SELECT query and see which indexes are summoned for query execution. Indexes that do not appear as a chosen index can be eliminated (dropped) over the course of time.

Here were questions you asked?

If i INDEX both on a_level, b_level (and of course item_id) and SELECT WHERE a_level= b_level= item_id= will it be bad? I guess only INDEX on b_level and item_id and SELECT WHERE b_level= AND item_id= will be enough/the best solution?

Those queries are not bad given the indexes I just presented.

The only reason why the item_id appears in all the indexes is to make those indexes covering indexes. That way, the retrieval of data is done using the index only, bypassing the table.

Mysql – How to auto assign ordinal ranking in thesql with as minimal queries for new rows

If you are content with adding and deleting a view_id per user_id, here is what I see

Adding View 12 To User 10

I see three queries

Insert new view into user with BogusRank
Move all rank down below NewRank
Change BogusRank to New Rank for new view

Here is the proposed code

SET @view_id = 12;
SET @user_id = 10;
SET @newrank = 3;
SET @defrank = 999999999;
INSERT INTO userviewranks
(view_id,user_id,ordinal_ranking)
VALUES (@view_id,@user_id,@defrank);
UPDATE userviewranks SET ordinal_ranking = ordinal_ranking + 1
WHERE view_id = @view_id AND user_id = @user_id AND ordinal_ranking >= @newrank;
UPDATE userviewranks SET ordinal_ranking = @newrank
WHERE view_id = @view_id AND user_id = @user_id AND ordinal_ranking = @defrank;

Deleting View 12 From User 10