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/show-variants.mysql | 17 +++++++---------- 1 file changed, 7 insertions(+), 10 deletions(-) (limited to 'DataBase/show-variants.mysql') diff --git a/DataBase/show-variants.mysql b/DataBase/show-variants.mysql index 3eeeeac..8909494 100644 --- a/DataBase/show-variants.mysql +++ b/DataBase/show-variants.mysql @@ -8,16 +8,13 @@ USE lastfm * 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 ) ; +CREATE TEMPORARY TABLE variants (SELECT username, type, COUNT(username) AS count FROM badges GROUP BY username, type ) ; -SELECT badges.username, badges.type, badges.style, badges.color, badges.hits, tmp.count - 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 ) +SELECT badges.username, badges.type, badges.style, badges.color, badges.hits, variants.count + FROM badges, variants + WHERE badges.username = variants.username + AND ( (badges.type = variants.type AND variants.count > 1) + OR ( (badges.type LIKE concat(variants.type,"%") OR variants.type LIKE concat(badges.type,"%")) + AND badges.type != variants.type ) ) ; -DROP TABLE tmp ; - -- cgit v1.2.3