Efficiently store last X records per item

Question

I want to store the last X records in an MySQL database in an efficient way. So when the 4th record is stored the first should be deleted.

The way I do this not is first run a query getting the items. Than check what I should do then insert/delete.

There has to be a better way to do this. Any suggestions?

Edit

I think I should add that the records stored do not have a unique number. They have a mixed par. For example article_id and user_id.

Then I want to make a table with the last X items for user_x.

Just selecting the article from the table grouped by user and sorted by time is not an option for me. The table where I do the sort and group on has millions of records and gets hit a lot for no reason. So making a table in between with the last X records is way more effient.

PS. I am not using this for articles and users.

Answer 1

Keep the primary keys of the first three items in a file or other persistent storage, then do an update statement instead of delete/insert?

If you want to put all in the DB, then I would add a unique sequence number to all records in the in-between table (lets call it seqid), then still perform a query but instead of getting whole rows, only get the sequence number e.g.

SELECT seqid from inbetweentable where article_id=? and user_id=?

Order that in your programming language (lets say PHP) and then do an update

UPDATE inbetweentable SET seqid=BIGGESTID+1, ... WHERE seqid=SMALLESTID 

(BIGGESTID and SMALLESTID come from your PHP code)

Edit: To only return one value (a comma seperated String) from the SQL statement do:

 SELECT GROUP_CONCAT(seqid) as idsCsv from inbetweentable where article_id=? and user_id=? ORDER BY seqid

and parse this in PHP. This will spare the looping code on mysql and PHP side and should be faster. e.g.

<?php
// Get single row
... 
$seqIds = explode(',', $row['idsCsv']);

Answer 2

I think you should do the following in a transaction:

1. Insert new records
2. Get ids of the excess records
3. Delete records with the ids from the step 2 if any

It would be great if you could merge steps 2 and 3 in a single query but this does not seem to be possible, since you will need to sort on and delete from the same table which is not allowed.

Here are some hints:

CREATE TEMPORARY TABLE items (
  item_id int unsigned not null auto_increment,
  data_ varchar(5) not null,
  primary key(item_id)
);

INSERT INTO items (data_)
  VALUES ('data1'), ('data2'), ('data3'), ('data4'), ('data5'), ('data6');

# select ids of the excess items
SELECT item_id
  FROM items, (select @cnt:=0) as cnt
  WHERE IF((@cnt:=@cnt+1)<=3, 0, 1)
  ORDER BY item_id DESC;

The last query will return:

+-------+
|item_id|
+-------+
|   3   |
|   2   |
|   1   |
+-------+

Answer 3

Implement it in a stored procedure (table is named ibt which stand for in-between-table):

delimiter ;
DROP TABLE IF EXISTS `ibt`;
CREATE TABLE `ibt` (
  `seqid` int(10) unsigned NOT NULL auto_increment,
  `article_id` varchar(10) NOT NULL default '',
  `user_id` varchar(10) NOT NULL default '',  
   anotherVar VARCHAR(10),
  PRIMARY KEY  (`article_id`,`user_id`),
  KEY `seqid` (`seqid`)
) ENGINE=MEMORY AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;


drop procedure if exists addEntry;
delimiter $$
create procedure addEntry(_article_id INT, _user_id INT, _anotherVar VARCHAR(10))
begin
  DECLARE done INT DEFAULT 0;
  declare seq INT;    
  declare seqNew INT DEFAULT 1;  
  declare Cnt INT DEFAULT 0;  

  declare cur CURSOR for
      SELECT seqid
      from ibt 
      where user_id=_user_id   
      order by seqid desc;  
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  START TRANSACTION;

  open cur;  
  REPEAT  
    FETCH cur INTO seq;    
    IF NOT done THEN        
      SET Cnt = Cnt+1;       
      IF Cnt = 3 THEN            
         DELETE FROM `ibt` where seqid = seq;
      END IF;      
      IF Cnt = 1 THEN            
         SET seqNew = seq + 1;
      END IF;      
    END IF;
  UNTIL done END REPEAT;

  INSERT into `ibt` 
  SET article_id=_article_id, 
        user_id=_user_id,  
      seqid=seqNew,      
      anotherVar=_anotherVar;

  close cur;  

  COMMIT;
end $$

delimiter ;

call addEntry(1, 1, 'a');
call addEntry(2, 1, 'b');
call addEntry(3, 1, 'c');
call addEntry(4, 1, 'd');

You can run the above SQL as a unit to test. I've used HeidiSQL.

Once you have the stored procedure in your DB you can do "call addEntry" from your PHP code.