diff options
| author | Hugues Hiegel <hugues@hiegel.fr> | 2011-07-13 12:32:35 +0200 |
|---|---|---|
| committer | Hugues Hiegel <hugues@hiegel.fr> | 2011-07-13 12:32:35 +0200 |
| commit | 958befb4f7eef5aa85a0721f908786d351840e04 (patch) | |
| tree | c41f6b9e62c886e4a659e93f0b8647947b3ff818 /DataBase/show-variants.mysql | |
| parent | f1c8fca45d9948b91f18d1dca20068143d0e6742 (diff) | |
[DataBase] better cleanup scripts, very much faster, less CPU usage
Diffstat (limited to 'DataBase/show-variants.mysql')
| -rw-r--r-- | DataBase/show-variants.mysql | 17 |
1 files changed, 7 insertions, 10 deletions
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 ; - |