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/cleanup.mysql | 30 +++++++++++++++++++++--------- 1 file changed, 21 insertions(+), 9 deletions(-) (limited to 'DataBase/cleanup.mysql') diff --git a/DataBase/cleanup.mysql b/DataBase/cleanup.mysql index b0ad464..aa51af5 100644 --- a/DataBase/cleanup.mysql +++ b/DataBase/cleanup.mysql @@ -1,14 +1,26 @@ USE lastfm -CREATE TEMPORARY TABLE users_having_a_badge (SELECT username, 0 AS has_a_badge FROM users) ; -CREATE TEMPORARY TABLE badges_having_a_user (SELECT username, 0 AS has_a_user FROM badges) ; +/** + * Deletes all entries in badges + * for each unique username + type + * where more than one style or color exists + */ +CREATE TEMPORARY TABLE duplicates (SELECT username, type, COUNT(username) AS count FROM badges GROUP BY username, type ) ; +DELETE badges.* + FROM badges, duplicates + WHERE badges.username = duplicates.username + AND ( (badges.type = duplicates.type AND duplicates.count > 1) + OR ( (badges.type LIKE concat(duplicates.type,"%") OR duplicates.type LIKE concat(badges.type,"%")) + AND badges.type != duplicates.type ) + ) ; -UPDATE users_having_a_badge, badges SET has_a_badge = 1 WHERE users_having_a_badge.username = badges.username ; -UPDATE badges_having_a_user, users SET has_a_user = 1 WHERE badges_having_a_user.username = users.username ; +/** + * Delete orphaned users and orphaned badges + */ +CREATE TEMPORARY TABLE users_without_badge (SELECT users.username FROM users LEFT JOIN badges ON users.username = badges.username WHERE badges.username IS NULL) ; +CREATE TEMPORARY TABLE badges_without_user (SELECT badges.username FROM badges LEFT JOIN users ON badges.username = users.username WHERE users.username IS NULL) ; + +DELETE users.* FROM users, users_without_badge where users.username = users_without_badge.username ; +DELETE badges.* FROM badges, badges_without_user where badges.username = badges_without_user.username ; -DELETE users.* FROM users, users_having_a_badge where has_a_badge = 0 AND users.username = users_having_a_badge.username ; -SELECT badges.png FROM badges, badges_having_a_user where has_a_user = 0 AND badges.username = badges_having_a_user.username ; -DELETE badges.* FROM badges, badges_having_a_user where has_a_user = 0 AND badges.username = badges_having_a_user.username ; -DROP TABLE users_having_a_badge; -DROP TABLE badges_having_a_user; -- cgit v1.2.3