Hello.

Tell me, please, how to implement such a request. Table structure:

USERNAME | DATETIME | IP_ADDRESS user0 user0 user0 user1 user1 user1 user2 user2 user2 ... userN userN userN CREATE TABLE `log` ( `USERNAME` VARCHAR(50) NOT NULL, `DATETIME` DATETIME NOT NULL, `IP_ADDRESS` VARCHAR(15) NOT NULL ) COLLATE='utf8_general_ci' ENGINE=MyISAM;

I need for each user in the table was not more than 100 records. Accordingly, if there are more, the earlier ones should be removed.

Here such request as works

 delete from `log` where `USERNAME` = ? and `DATETIME` not in (select `DATETIME` from (select * from `log` order by `DATETIME` desc limit 100) s )

but I cannot repeat it for each USERNAME, as there are several thousand of them.

  • Try this: create temporary table log1 (USERNAME varchar (50), DATETIME DATETIME, number int); insert into log1 (USERNAME, DATETIME , number) select USERNAME, DATETIME , if (l.USERNAME = @un, @rn: = @rn + 1, @rn: = 1 + least (0, @un: = l.USERNAME )) as number from log l, (select @rn: = 0, @un = '_') zz order by USERNAME, DATETIME desc; delete log from log join log1 on log.USERNAME = log1.USERNAME and log.DATETIME = log1.DATETIME where log1.number> 100; Of course, everything can be pushed into one request, but it will look awful. - alexlz
  • Table for 350,000 entries. After 3 hours, the removal request has not yet completed, dropped. - mmp0sa
  • four
    Review storage structures - enter the record count for the user, if it is 100, then by adding a new record delete the oldest one. - ReinRaus
  • And if indexes add? And, by the way, which request was so long? - alexlz
  • 350 thousand is not so much to run 3 hours. Most likely there really is not enough indexes. If you need to do this one-time or at a certain periodicity (not in real time), you can create a table with a similar structure, insert in it the data that you do not need to delete, add the necessary indexes, then drop the original table and rename the new in the original name. - vanchester

2 answers 2

Judging by the question, the author needs to perform such cleaning regularly, therefore I advise you to create a stored procedure for deleting logs. Inside it, to do the removal step by step, with a minimum of deleted records per request, in my example 100 records.

 CREATE PROCEDURE logclean() BEGIN DECLARE done INT DEFAULT FALSE; DECLARE users VARCHAR(50); DECLARE count_rows, delete_rows INT; DECLARE del CURSOR FOR SELECT `USERNAME`, Count(*) - 100 FROM `log` GROUP BY `USERNAME` HAVING Count(*) > 100; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; OPEN del; read_loop: LOOP FETCH del INTO users, count_rows; IF done THEN LEAVE read_loop; END IF; SET delete_rows = 100; WHILE (count_rows > 0) DO IF delete_rows > count_rows THEN SET delete_rows = count_rows; END IF; DELETE FROM `log` WHERE `USERNAME` = users ORDER BY `DATETIME` LIMIT delete_rows; SET count_rows = count_rows - ROW_COUNT(); END WHILE; END LOOP; CLOSE del; END;

Here is an example in SQL Fiddle .

Ps. The request given in the question is incorrect, because the subquery does not take into account the user specified in the condition.

    Try this http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/ .

    The article is entitled "How to select the first / least / max row per group in SQL"

    • Include highlights from the link directly in the answer. Link may rot, and the shutter speed will remain. - andreycha