Effectively store the last X records per item

Question

I want to efficiently store the latest X entries in a MySQL database. Therefore, when the fourth record is saved, the first must be deleted.

The way I do this does not first start the request receiving the elements. Then check what I should do and then insert / remove.

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

Edit

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

Then I want to create a table with the last X elements for user_x.

Just selecting an article from a table grouped by user and sorted by time is not an option for me. The table in which I am sorting and grouping contains millions of records and gets many reasons for no reason. Thus, creating a table between the last X entries is more efficient.

PS. I do not use this for articles and users.

Answer 1

Implement it in a stored procedure (the table is called ibt, which stands between the 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 for testing. I used HeidiSQL.

Once you have a stored procedure in your database, you can "call addEntry" from your PHP code.

Answer 2

, update delete/insert?

, ( seqid), , , , ,

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

, (, PHP),

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

(BIGGESTID SMALLESTID PHP-)

Edit: ( ) SQL:

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

PHP. mysql PHP . .

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

Answer 3

, :

• Delete records with identifiers from step 2, if any

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

Here are some suggestions:

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   |
+-------+