noname1.sql
# Query_time: 2.490641 Lock_time: 0.000598 Rows_sent: 1088 Rows_examined: 601259
SELECT * FROM
(SELECT *
FROM (SELECT @row:=@row+1 rank, s.* FROM
(SELECT @row:=0) r,
(SELECT u.id, u.username, u.hideseed, f.id friend, g.id myfriend
FROM (SELECT s.id, s.userid, @rank:=@rank+1 rank, @week+0 week
FROM (SELECT @rank:=0, @week:=0, @time:=0) r,
(SELECT s.id, s.userid, UNIX_TIMESTAMP(s.datum) time
FROM bonuslog s
WHERE s.msg LIKE 'Weekly bonus %' ORDER BY s.id ASC
) s
WHERE IF(@time = s.time, @rank < 25, NOT IF(@time:=s.time, IF(@week:=@week+1, @rank:=0, 0), 0))) s
INNER JOIN users u ON u.id = s.userid AND u.enabled = 'yes' AND u.hidestats = 'no' AND u.id != 31407
LEFT JOIN friends f ON u.id = f.userid AND f.friendid = '62631'
LEFT JOIN friends g ON u.id = g.friendid AND g.userid = '62631'
GROUP BY u.id HAVING SUM(s.rank > 0 AND s.rank <= 25) > 0
ORDER BY
SUM(s.rank > 0 AND s.rank <= 25) DESC,
SUM(s.rank > 0 AND s.rank <= 3) DESC,
SUM(s.rank > 3 AND s.rank <= 10) DESC,
SUM(s.rank > 10 AND s.rank <= 25) DESC,
SUM(s.rank = 1) DESC,
SUM(s.rank = 2) DESC,
SUM(s.rank = 3) DESC,
SUM(s.rank = 4) DESC,
SUM(s.rank = 5) DESC,
SUM(s.rank = 6) DESC,
SUM(s.rank = 7) DESC,
SUM(s.rank = 8) DESC,
SUM(s.rank = 9) DESC,
SUM(s.rank = 10) DESC,
SUM(s.rank = 11) DESC,
SUM(s.rank = 12) DESC,
SUM(s.rank = 13) DESC,
SUM(s.rank = 14) DESC,
SUM(s.rank = 15) DESC,
SUM(s.rank = 16) DESC,
SUM(s.rank = 17) DESC,
SUM(s.rank = 18) DESC,
SUM(s.rank = 19) DESC,
SUM(s.rank = 20) DESC,
SUM(s.rank = 21) DESC,
SUM(s.rank = 22) DESC,
SUM(s.rank = 23) DESC,
SUM(s.rank = 24) DESC,
SUM(s.rank = 25) DESC,
MAX(s.week) ASC
) s
) s
WHERE s.rank <= 26 OR s.id = '62631' OR s.friend OR s.myfriend
) head
LEFT JOIN (
SELECT u.id,
36 type,
s.rank value,
NULL name,
IF(s.rank > 10, IF(s.rank > 15, 1, -1), IF(s.rank > 3, IF(s.rank > 5, 2, -2), IF(s.rank > 1, 3, -3))) class,
s.week sort
FROM (
SELECT s.id, s.userid, @rank:=@rank+1 rank, @week+0 week
FROM ( SELECT @rank:=0, @week:=0, @time:=0) r,
( SELECT s.id, s.userid, UNIX_TIMESTAMP(s.datum) time
FROM bonuslog s
WHERE s.msg LIKE 'Weekly bonus %' ORDER BY s.id ASC) s
WHERE IF(@time = s.time, @rank < 25, NOT IF(@time:=s.time, IF(@week:=@week+1, @rank:=0, 0), 0))
) s
INNER JOIN users u ON u.id = s.userid AND u.enabled = 'yes' AND u.hidestats = 'no' AND u.id != 31407
UNION ALL SELECT u.id, 37 type, s.rank value, NULL name, IF(s.rank > 10, IF(s.rank > 15, 1, -1), IF(s.rank > 3, IF(s.rank > 5, 2, -2), IF(s.rank > 1, 3, -3))) class, s.rank sort
FROM (SELECT @pred id, u.id userid, @pred:=@pred+1 rank FROM (SELECT @pred:=0) r, users u WHERE u.enabled = 'yes' AND u.bonuspott > 0 ORDER BY u.bonuspott DESC, u.id ASC LIMIT 25) s
INNER JOIN users u ON u.id = s.userid AND u.enabled = 'yes' AND u.hidestats = 'no' AND u.id != 31407
UNION ALL SELECT u.id, IF(v.enabled = 'yes', 31, -31) type, v.id value, v.username name, ((v.hideseed = 'no') * 2 - 1) * IF(v.gender = 'Female', 11, v.class + 1) class, s.id sort FROM friends s
INNER JOIN users u ON u.id = s.friendid AND u.enabled = 'yes' AND u.hidestats = 'no' AND u.id != 31407
INNER JOIN users v ON v.id = s.userid
UNION ALL SELECT u.id, IF(v.enabled = 'yes', 32, -32) type, v.id value, v.username name, ((v.hideseed = 'no') * 2 - 1) * IF(v.gender = 'Female', 11, v.class + 1) class, s.id sort FROM blocks s
INNER JOIN users u ON u.id = s.blockid AND u.enabled = 'yes' AND u.hidestats = 'no' AND u.id != 31407
INNER JOIN users v ON v.id = s.userid
UNION ALL SELECT u.id, IF(v.enabled = 'yes', 33, -33) type, v.id value, v.username name, ((v.hideseed = 'no') * 2 - 1) * IF(v.gender = 'Female', 11, v.class + 1) class, v.id sort FROM users v
INNER JOIN users u ON u.id = v.inviter AND u.enabled = 'yes' AND u.hidestats = 'no' AND u.id != 31407
UNION ALL SELECT CASE x.type WHEN 0 THEN u.id WHEN 1 THEN v.id END id, CASE x.type WHEN 0 THEN IF(v.enabled = 'yes', 34, -34) WHEN 1 THEN IF(v.enabled = 'yes', 35, -35) END type, CASE x.type WHEN 0 THEN v.id WHEN 1 THEN u.id END value, CASE x.type WHEN 0 THEN v.username WHEN 1 THEN u.username END name, CASE x.type WHEN 0 THEN ((v.hideseed = 'no') * 2 - 1) * IF(v.gender = 'Female', 11, v.class + 1) WHEN 1 THEN ((u.hideseed = 'no') * 2 - 1) * IF(u.gender = 'Female', 11, u.class + 1) END class, s.id sort FROM bonuslog s
INNER JOIN users u ON u.id = s.userid AND s.msg LIKE 'Purchase of a heart to %'
INNER JOIN users v ON v.id = CAST(SUBSTR(s.msg, 53, LOCATE('"', s.msg, 53) - 53) AS UNSIGNED)
CROSS JOIN (SELECT 0 type UNION ALL SELECT 1 type) x) body USING (id)
ORDER BY head.rank ASC, body.type ASC, body.sort ASC;