Optimising MySQL Query, Select within Select, Multiple of same

Question

I need help optimising this MySQL statement that I whipped up. It does exactly what I want, however I have a great feeling that it'll be quite slow, since I do multiple selects within the statement, and I also query achievements_new multiple times. This is the first time I do some major statement like this, I'm used to the simple SELECT FROM WHERE style crap.

I might do some explaining, this is for a leaderboard style thing for my website.

--First variable output is a rank that is calculated according to the formula shown, (Log + Log + # of achievements).

--Wepvalue is the sum of the values of the weapons which that id has. playerweapons contains all the weapons, and weaponprices convert the type to the price, and then the SUM calculates the value.

--Achcount is simply the amount of achievements that's unlocked. Maybe this can be optimised somehow with the rank output?

--id in achievements_new and playerweapons are Foreign Keys to the id in playerdata

SELECT
    (
        IFNULL(LOG(1.5, cashearned),0) +
        IFNULL(LOG(1.3, roundswon), 0) +
        (
            SELECT COUNT(*)
            FROM achievements_new
            WHERE `value` = -1 AND achievements_new.id = playerdata.id
        )
    ) as rank,
    nationality,
    nick,
    steamid64,
    cash,
    playtime,
    damage,
    destroyed,
    (
        SELECT SUM(price)
        FROM weaponprices
        WHERE weapon IN
        (
            SELECT class
            FROM playerweapons
            WHERE playerweapons.id = playerdata.id
        )
    ) as wepvalue,
    (
        SELECT COUNT(*)
        FROM achievements_new
        WHERE `value` = -1 AND achievements_new.id = playerdata.id
    ) as achcount,
    lastplayed
FROM playerdata
ORDER BY rank DESC

Table structures:

playerdata:

CREATE TABLE IF NOT EXISTS `playerdata` (
    `id` int(11) unsigned NOT NULL,
  `steamid64` char(17) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  `nick` varchar(32) NOT NULL DEFAULT '',
  `cash` int(32) unsigned NOT NULL DEFAULT '0',
  `playtime` int(32) unsigned NOT NULL DEFAULT '0',
  `nationality` char(2) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  `damage` int(32) unsigned NOT NULL DEFAULT '0',
  `destroyed` int(32) unsigned NOT NULL DEFAULT '0',
  `cashearned` int(10) unsigned NOT NULL,
  `roundswon` smallint(5) unsigned NOT NULL,
  `lastplayed` datetime NOT NULL,
) ENGINE=InnoDB

achievements_new:

CREATE TABLE IF NOT EXISTS `achievements_new` (
  `id` int(10) unsigned NOT NULL,
  `achkey` enum(<snip - lots of values here>) NOT NULL,
  `value` mediumint(8) NOT NULL DEFAULT '0'
) ENGINE=InnoDB

playerweapons:

CREATE TABLE IF NOT EXISTS `playerweapons` (
  `id` int(10) unsigned NOT NULL,
  `class` varchar(30) CHARACTER SET ascii NOT NULL
) ENGINE=InnoDB

weaponprices:

CREATE TABLE IF NOT EXISTS `weaponprices` (
  `weapon` varchar(30) NOT NULL,
  `price` int(10) unsigned NOT NULL
) ENGINE=InnoDB

Thanks in advance!

Answer 1

Your query is fairly reasonable, although I would rewrite the subqueries to use explicit joins rather than in and factor out the achievements subquery:

SELECT (IFNULL(LOG(1.5, cashearned),0) + IFNULL(LOG(1.3, roundswon), 0) +
        coalesce(an.cnt, 0)
       ) as rank,
       nationality, nick, steamid64, cash, playtime, damage, destroyed,
       (SELECT SUM(wp.price)
        FROM weaponprices wp JOIN
             playerweapons pw
             on pw.class = wp.weapons
        WHERE pw.id = pd.id
       ) as wepvalue,
       coalesce(an.cnt, 0) as achcount,
       lastplayed
FROM playerdata pd left outer join
     (SELECT id, count(*) as cnt
      FROM achievements_new an
      WHERE an.`value` = -1 
      GROUP BY an.id
     ) an
     on an.id = pd.id
ORDER BY rank DESC;

For this query, create the following indexes:

playerweapons(id, weapon);
weaponprices(class, price);
achievements_new(value, id);

This does the following things:

• It eliminates two redundant subqueries on achievements_new.
• It should optimize the prices subquery to only use indexes.
• It replaces the in with an explicit join, which is sometimes optimized better.
• It does not require an outer group by.

Answer 2

I would try to remove all correlated subqueries

SELECT
      ( COALESCE(LOG(1.5, pd.cashearned), 0)
      + COALESCE(LOG(1.3, pd.roundswon), 0)
      + COALESCE(an.cnt, 0))   AS rank
    , pd.nationality
    , pd.nick
    , pd.steamid64
    , pd.cash
    , pd.playtime
    , pd.damage
    , pd.destroyed
    , COALESCE(pw.wepvalue, 0) AS wepvalue
    , COALESCE(an.cnt, 0)      AS achcount
    , pd.lastplayed
FROM playerdata pd
      LEFT JOIN (
                  SELECT
                        id
                      , COUNT(*) AS cnt
                  FROM achievements_new
                  WHERE value = -1
                  GROUP BY
                        id
            ) an
                  ON pd.id = an.id
      LEFT JOIN (
                  SELECT
                        playerweapons.id
                      , SUM(price) AS wepvalue
                  FROM weaponprices
                        INNER JOIN playerweapons
                                    ON weaponprices.weapon = playerweapons.class
                  GROUP BY
                        playerweapons.id
            ) pw
                  ON pd.id = pw.id
ORDER BY
      rank DESC;

Answer 3

Try something like the query below.
I used LEFT JOIN instead of joins because there may be players without achievements or weapons. If you do not need these players you can use JOIN

SELECT
    IFNULL(LOG(1.5, p.cashearned),0) +
        IFNULL(LOG(1.3, p.roundswon), 0) +
        SUM(CASE WHEN ac.id IS NOT NULL THEN 1 ELSE 0 END)/COUNT(pw.id) as rank
    p.nationality,
    p.nick,
    p.steamid64,
    p.cash,
    p.playtime,
    p.damage,
    p.destroyed,
    --SUM(CASE WHEN pw.id IS NOT NULL THEN pw.price ELSE 0 END) as wepvalue,
    --wpn.price as wepvalue,
    SUM(CASE WHEN pw.id IS NOT NULL THEN wp.price ELSE 0 END)/COUNT(ac.id) as wepvalue,
    SUM(CASE WHEN ac.id IS NOT NULL THEN 1 ELSE 0 END)/COUNT(pw.id) as achcount,
    lastplayed
FROM playerdata as p
    JOIN playerweapons as pw ON pw.id = p.id
    JOIN weaponprices as wp ON pw.class = wp.weapon
    LEFT JOIN achievements_new as ac ON ac.id = p.id AND ac.value = -1
    --LEFT JOIN playerweapons as pw ON pw.id = p.id
    --LEFT JOIN weaponprices as wp ON pw.class = wp.weapon
    --LEFT JOIN (   SELECT 
                      --pw.id as player,
                      --SUM(wp.price) as price
                  --FROM weaponprices as wp
                      --JOIN playerweapons as pw ON pw.class = wp.weapon 
                  --GROUP BY pw.id
              --) as wpn ON wpn.player = p.id
GROUP BY 
    p.nationality,
    p.nick,
    p.steamid64,
    p.cash,
    p.playtime,
    p.damage,
    p.destroyed,
    p.lastplayed