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

Query Explain Thanks in advance!

Show the explain also.That subquery with IN can be written as a JOIN. — Mihai
– Mihai, Commented Aug 22, 2014 at 19:38
I'm aware of JOINS, just not how to use them properly in this case. — Daniel
– Daniel, Commented Aug 22, 2014 at 19:40

user2989408 · Accepted Answer · 2014-08-22 20:43:34Z

1

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

edited Aug 22, 2014 at 20:43

answered Aug 22, 2014 at 19:49

user2989408

3,1371 gold badge20 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Daniel Over a year ago

It appears you missed out the "weaponprices" table entirely, otherwise this looks good!

Daniel Over a year ago

Not sure why but my whole MySQL server froze up after calling this query, had to force restart it. :(

Daniel Over a year ago

-scratch that- It seems to cause extreme values on all sums, wepvalue is up to 10 millions where maximum possible is 315k. Achcount is up to the hundreds where maximum is 31.

user2989408 Over a year ago

Whenever you use aggregate functions like SUM, AVG or COUNT you need to use the GROUP BY clause.

user2989408 Over a year ago

That is happening because there can be multiple achievements and weapons for each player. Lets say a player has m weapons and n achievements then the weapons price will be nmprice of each weapon. A solution would be to divide by the number of achievements. I will add it to the answer.

Gordon Linoff · Accepted Answer · 2014-08-23 15:39:36Z

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.

edited Aug 23, 2014 at 15:39

answered Aug 22, 2014 at 20:36

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

Paul Maxwell Over a year ago

on pw.weapon = wp.class should read on wp.weapon = pw.class I believe. But for my curiosity: Why is this correlated subquery retained?

Paul Maxwell · Accepted Answer · 2014-08-23 04:40:36Z

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;

Collectives™ on Stack Overflow

Optimising MySQL Query, Select within Select, Multiple of same

3 Answers 3

5 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related