From 958befb4f7eef5aa85a0721f908786d351840e04 Mon Sep 17 00:00:00 2001 From: Hugues Hiegel Date: Wed, 13 Jul 2011 12:32:35 +0200 Subject: [DataBase] better cleanup scripts, very much faster, less CPU usage --- DataBase/remove-badges-variants.mysql | 32 -------------------------------- 1 file changed, 32 deletions(-) delete mode 100644 DataBase/remove-badges-variants.mysql (limited to 'DataBase/remove-badges-variants.mysql') diff --git a/DataBase/remove-badges-variants.mysql b/DataBase/remove-badges-variants.mysql deleted file mode 100644 index 3f29d2a..0000000 --- a/DataBase/remove-badges-variants.mysql +++ /dev/null @@ -1,32 +0,0 @@ - -USE lastfm - - -/** - * Deletes all entries in badges - * for each unique username + type - * where more than one style or color exists - */ - -DROP TABLE IF EXISTS tmp ; -CREATE TEMPORARY TABLE tmp (SELECT username, type, COUNT(username) AS count FROM badges GROUP BY username, type ) ; - -SELECT badges.png - FROM badges, tmp - WHERE badges.username = tmp.username - AND ( (badges.type = tmp.type AND tmp.count > 1) - OR ( (badges.type LIKE concat(tmp.type,"%") OR tmp.type LIKE concat(badges.type,"%")) - AND badges.type != tmp.type ) - ) ; - -DELETE badges.* - FROM badges, tmp - WHERE badges.username = tmp.username - AND ( (badges.type = tmp.type AND tmp.count > 1) - OR ( (badges.type LIKE concat(tmp.type,"%") OR tmp.type LIKE concat(badges.type,"%")) - AND badges.type != tmp.type ) - ) ; - - -DROP TABLE tmp ; - -- cgit v1.2.3