[RESOLVED] keep last 5 logs and delete rest

maskme

Dear All,

How can I keep last 5 logs from a table and delete the rest.

I've a table which records logs of people logging to the site every time and displays them them when they last logged in.

Since my table is becoming HUGE, i want to just keep the last 5 records of a Particular login and delete the rest.

How is it possible using MySQL query???

Any idea, please help
Thanks

Roger_Ramjet

OK, the first problem to solve is to give an order or index no to each users' log entries.

Assuming that each entry has an autoinc logid we can number the sequence thus

SELECT t1.userid, COUNT(t1.logid) AS c, t1.logid, t1.logid > = t2.logid AS flg
FROM log t1 INNER JOIN log t2 ON t1.userid = t2.userid
GROUP BY t1.userid, t1.logid, flg
HAVING flg = TRUE

This will return results with each user's log entry numbered in sequence; save it as logview1

Next we need the max sequence no for each user

SELECT userid, MAX(c) AS m FROM logview1
GROUP BY userid

// this expands to

SELECT userid, MAX(c) AS m FROM 
(SELECT t1.userid, COUNT(t1.logid) AS c, t1.logid, t1.logid > = t2.logid AS flg
FROM log t1 INNER JOIN log t2 ON t1.userid = t2.userid
GROUP BY t1.userid, t1.logid, flg
HAVING flg = TRUE)
GROUP BY userid

Save this as logview2

Now we can use the 2 queries to get the last 5 logid's for each user

SELECT q1.userid, q1.logid
FROM  logview1 q1 INNER JOIN logview2 q2 ON q1.userid = q2.userid
WHERE (q2.m - q1.c) < 5 

// this expands as 

SELECT q1.userid, q1.logid
FROM 
(SELECT t1.userid, COUNT(t1.logid) AS c, t1.logid, t1.logid > = t2.logid AS flg
FROM log t1 INNER JOIN log t2 ON t1.userid = t2.userid
GROUP BY t1.userid, t1.logid, flg
HAVING flg = TRUE) AS q1
INNER JOIN 
(SELECT userid, MAX(c) AS m FROM 
(SELECT t1.userid, COUNT(t1.logid) AS c, t1.logid, t1.logid > = t2.logid AS flg
FROM log t1 INNER JOIN log t2 ON t1.userid = t2.userid
GROUP BY t1.userid, t1.logid, flg
HAVING flg = TRUE)
GROUP BY userid) AS q2
ON q1.userid = q2.userid
WHERE (q2.m - q1.c) < 5

Might be some syntax errors in there but basically that's it. Save as logview3

Now we can finally do the delete

DELETE FROM log
WHERE logid NOT IN (SELECT logid FROM logview3)

maskme

Hi Roger,

Thanks for this, it works like a charm in my Local Host where MySQL version is 5, since it supports views.

Otherwise my Online Web Server does not support VIEW, so its disappointing.

Secondly it does take lot of time to generate View 1 and View 2.

Any other way is their directly through Delete Query rather than create 3 Views, since my Online Web Server does not support View.

Thanks Anyways 🙂

maskme

Hi Rogers

Further to this, I combined all the queries into one Delete by copy pasting your Query. But it gives error: 1248 - Every Derived Table Must Have its Own Alias in Navicat.

DELETE FROM tbl_user_login_log 
WHERE fld_typeofv = 'P' and fld_vid NOT IN (SELECT q1.fld_userid, q1.fld_vid 
FROM 
(SELECT t1.fld_userid, COUNT(t1.fld_vid) AS c, t1.fld_vid, t1.fld_vid >= t2.fld_vid AS flg 
FROM tbl_user_login_log as  t1 INNER JOIN tbl_user_login_log AS t2 ON t1.fld_userid = t2.fld_userid 
GROUP BY t1.fld_userid, t1.fld_vid, flg 
HAVING flg = TRUE) AS q1 
INNER JOIN 
(SELECT fld_userid, MAX(c) AS m FROM 
(SELECT t1.fld_userid, COUNT(t1.fld_vid) AS c, t1.fld_vid, t1.fld_vid >= t2.fld_vid AS flg 
FROM tbl_user_login_log t1 INNER JOIN tbl_user_login_log t2 ON t1.fld_userid = t2.fld_userid 
GROUP BY t1.fld_userid, t1.fld_vid, flg 
HAVING flg = TRUE) 
GROUP BY fld_userid) AS q2 
ON q1.fld_userid = q2.fld_userid 
WHERE (q2.m - q1.c) < 5 )

Any Idea, where I'm going wrong???

Weedpacket

This derived table:

(SELECT t1.fld_userid, COUNT(t1.fld_vid) AS c, t1.fld_vid, t1.fld_vid >= t2.fld_vid AS flg
FROM tbl_user_login_log t1 INNER JOIN tbl_user_login_log t2 ON t1.fld_userid = t2.fld_userid
GROUP BY t1.fld_userid, t1.fld_vid, flg
HAVING flg = TRUE)

doesn't have an alias (the other two do).

maskme

Thanks weedpacket, but how and what alias should i give and where???

Sorry I really have not worked on such a complex query ever, your expertise would help.

Please let me know
Thanks

Roger_Ramjet

Here:

DELETE FROM tbl_user_login_log
WHERE fld_typeofv = 'P' and fld_vid NOT IN (SELECT q1.fld_userid, q1.fld_vid
FROM
(SELECT t1.fld_userid, COUNT(t1.fld_vid) AS c, t1.fld_vid, t1.fld_vid >= t2.fld_vid AS flg
FROM tbl_user_login_log as  t1 INNER JOIN tbl_user_login_log AS t2 ON t1.fld_userid = t2.fld_userid
GROUP BY t1.fld_userid, t1.fld_vid, flg
HAVING flg = TRUE) AS q1
INNER JOIN
(SELECT fld_userid, MAX(c) AS m FROM
(SELECT t1.fld_userid, COUNT(t1.fld_vid) AS c, t1.fld_vid, t1.fld_vid >= t2.fld_vid AS flg
FROM tbl_user_login_log t1 INNER JOIN tbl_user_login_log t2 ON t1.fld_userid = t2.fld_userid
GROUP BY t1.fld_userid, t1.fld_vid, flg
HAVING flg = TRUE) AS t  // <-- table alias here
GROUP BY fld_userid) AS q2
ON q1.fld_userid = q2.fld_userid
WHERE (q2.m - q1.c) < 5 )

No way to speed this up I'm afraid, it is what it is.

Roger_Ramjet

And of course I was so involved in demonstrating how to build a solution to a problem like this that I overlooked an obvious optimisation

SELECT userid, COUNT(logid) AS m FROM log
GROUP BY userid

That should be logview2 and should be a lot faster.

So:

DELETE FROM tbl_user_login_log
WHERE fld_typeofv = 'P' and fld_vid NOT IN (SELECT q1.fld_userid, q1.fld_vid
FROM
(SELECT t1.fld_userid, COUNT(t1.fld_vid) AS c, t1.fld_vid, t1.fld_vid >= t2.fld_vid AS flg
FROM tbl_user_login_log as  t1 INNER JOIN tbl_user_login_log AS t2 ON t1.fld_userid = t2.fld_userid
GROUP BY t1.fld_userid, t1.fld_vid, flg
HAVING flg = TRUE) AS q1
INNER JOIN
(SELECT fld_userid, COUNT(fld_vid) AS m FROM log
GROUP BY fld_userid) AS q2
ON q1.fld_userid = q2.fld_userid
WHERE (q2.m - q1.c) < 5 )

See how that runs now.

If you need to run this often and it is very slow then you should consider storing the sequence no and user count. These would be updated by an INSERT Trigger so every log entry is updated with it's sequence no and the user is updated with the total count. That would cause an overhead on each INSERT but that would be ofset by the gains in the DELETE.

maskme

Hi Roger

Thanks for your help once again.

The query took around 3 minutes, but at the end gave me a new error message (1241- Operand should contain 1 column(s)) - This is generated via Navicat 8.

What does that mean??? and where do i fix the Operand.

Please let me know
Thanks

maskme

DELETE FROM tbl_user_login_log 
WHERE fld_typeofv = 'P' and fld_vid NOT IN (SELECT q1.fld_userid,[COLOR="DarkRed"]q1.fld_vid[/COLOR] FROM (SELECT t1.fld_userid, COUNT(t1.fld_vid) AS c, t1.fld_vid, t1.fld_vid >= t2.fld_vid AS flg 
FROM tbl_user_login_log as  t1 INNER JOIN tbl_user_login_log AS t2 ON t1.fld_userid = t2.fld_userid 
GROUP BY t1.fld_userid, t1.fld_vid, flg 
HAVING flg = TRUE) AS q1 
INNER JOIN 
(SELECT fld_userid, COUNT(fld_vid) AS m FROM tbl_user_login_log 
GROUP BY fld_userid) AS q2 
ON q1.fld_userid = q2.fld_userid 
WHERE (q2.m - q1.c) < 5 )

From the above code, I removed q1.fld_vid and IT WORKED 🙂 But Unfortunately, it kept the Records of March and deleted all the records of April, May for all users 🙁 Why did it do this???? Any Answers?

DELETE FROM tbl_user_login_log 
WHERE fld_typeofv = 'P' and fld_vid NOT IN (SELECT q1.fld_userid FROM (SELECT t1.fld_userid, COUNT(t1.fld_vid) AS c, t1.fld_vid, t1.fld_vid >= t2.fld_vid AS flg 
FROM tbl_user_login_log as  t1 INNER JOIN tbl_user_login_log AS t2 ON t1.fld_userid = t2.fld_userid 
GROUP BY t1.fld_userid, t1.fld_vid, flg 
HAVING flg = TRUE) AS q1 
INNER JOIN 
(SELECT fld_userid, COUNT(fld_vid) AS m FROM tbl_user_login_log 
GROUP BY fld_userid) AS q2 
ON q1.fld_userid = q2.fld_userid 
WHERE (q2.m - q1.c) < 5 )

Roger_Ramjet

That is because you did this

SELECT q1.fld_userid FROM (

You selected the userid instead of the vid

By doing that you got every userid so the vid's with higher numbers, ie newer records, were deleted.

We want the 5 vid's for each user that we want to keep, not the userid's, they are of no use here. We then delete all the vid's NOT IN that list.

steps

1 Join the log to itself where each id in table 1 is joined to all the ids in table 2 that are less than or equal to it - for each user of course.
so post 1 is joined to itself = 1; post 2 is joined to itself and post 1 = 2; etc
That gives us our count or sequence no.

2 Get the highest number for each user

3 Subtract each sequence no from that highest number and those with a product less than 5 are the posts we want to keep, for each user.

maskme

First of all sorry for the late reply as I just saw today 25/05.

Thanks Roger for the insight and explanation of the complete query.

I've just tested the Query and It works like a charm and keeps only latest 5 records and deletes the rest, which is what i wanted.

Hats off to you !!!

Roger_Ramjet

Sorry for the bump on a Resolved thread but just in case anyone ever comes across this in a search, this query can be simplified even further by just reversing the sequence so that the log entries are sequenced in descending order: newest = 1, second newest = 2, etc. Then we do not have to worry about how many log entries each user has and can just delete all that have a sequence number greater than 5. WE can also optimise out the NOT IN comparison with a join instead.

DELETE FROM tbl_user_login_log INNER JOIN 
(SELECT t1.fld_userid, t1.fld_vid, COUNT(t1.fld_vid) AS c,  t1.fld_vid <= t2.fld_vid AS flg 
FROM tbl_user_login_log AS t1 INNER JOIN tbl_user_login_log AS t2 
USING(fld_userid) 
GROUP BY t1.fld_userid, t1.fld_vid, flg 
HAVING flg = TRUE) AS q1 
USING(fld_vid) 
WHERE fld_typeofv = 'P' AND q1.c > 5

It would be interesting to find out how this performs compared with the original query if maskme ever runs it.

PS this code is untested so don't be surprised if there are any typos or syntax errors.