CODEWITHSUNDEEP
mysql
Frantisek
Frantisek

Reputation: 7693

How do I SELECT data from a MySQL table and DELETE everything else?

I have a simple MySQL table like this:

CREATE TABLE `logins` (
  `user` int(10) NOT NULL,
  `time` int(10) NOT NULL,
  `ip` varchar(20) NOT NULL,
  PRIMARY KEY (`user`,`time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

With CRON, every 24 hours, I'm performing a cleanup on this table in a way that I keep only unique logins for every users's IP, i.e. if a certain user logins 5 times from IP X, I keep only the latest time he logged in with that IP and delete the remaining 4.

This is my QUERY:

DELETE FROM `logins` WHERE (`user`, `time`) NOT IN(
   SELECT `user`, `maxtime`
   FROM(
      SELECT `user`, MAX(`time`) as `maxtime` FROM `logins` GROUP BY `user`, `ip`
   ) as `a`
)

This query-subquery way is kind of slow. It takes around 3 seconds to complete.

However, the inner part is extremely quick. If I leave out only the SELECT part, like this:

SELECT `user`, MAX(`time`) as `maxtime` FROM `logins` GROUP BY `user`, `ip`

It takes less than 0.005 seconds.

So I was wondering: what if I did the opposite? Not 'delete all but X', but instead, 'select X, delete all, re-insert X'?

Is this possible?

Something like this:

1) SELECT `user`, MAX(`time`) as `maxtime` FROM `logins` GROUP BY `user`, `ip`
/* store this somewhere temporarily */

2) TRUNCATE TABLE `logins`

3) reinsert data from step 1 to `logins`

Upvotes: 3

Views: 80

Answers (2)

fthiella
fthiella

Reputation: 49049

I would use this to delete all record except the most recent for every combination of user and ip:

DELETE l1.*
FROM
  logins l1 INNER JOIN logins l2
  ON l1.user=l2.user
     AND l1.ip=l2.ip
     AND l1.time<l2.time

Please see fiddle here.

Upvotes: 1

Z .
Z .

Reputation: 12837

Use another (temporary or not) table to insert the data you want to keep. Truncate. Then insert it back... 

INSERT INTO LoginsTemp 
SELECT * FROM Logins WHERE ...; 

TRUNCATE Logins; 

INSERT INTO Logins 
SELECT * FROM LoginsTemp;

Upvotes: 2

Related Questions