MySQL Optimize Nested SELECT and INSERT Statement

Question

I'm trying to find any ways to optimize this statement:

INSERT INTO achievements 
(
    nick, cost, achievement_type, announced_in_chat, shown_on_stream, dt
)

SELECT nick, 2000, 0, TRUE, FALSE, NOW() 
FROM points_log

WHERE nick NOT IN 
(
    SELECT nick from achievements
    WHERE achievement_type = 0 AND stream_online = TRUE
)
GROUP BY nick HAVING SUM(amount) >= 2000;

The goal is to find people who have scored 2000 points (SUM(amount)) from the points_log and are also not in achievements (nick NOT IN). Any help would be greatly appreciated.

Here are the achievements and points_log tables:

mysql> describe achievements;
+-------------------+-----------------------+------+-----+---------+----------------+
| Field             | Type                  | Null | Key | Default | Extra          |
+-------------------+-----------------------+------+-----+---------+----------------+
| id                | mediumint(8) unsigned | NO   | PRI | NULL    | auto_increment |
| nick              | char(25)              | NO   | PRI | NULL    |                |
| cost              | decimal(8,4)          | NO   | MUL | NULL    |                |
| achievement_type  | tinyint(3) unsigned   | NO   | MUL | NULL    |                |
| announced_in_chat | tinyint(1)            | NO   |     | NULL    |                |
| shown_on_stream   | tinyint(1)            | NO   |     | NULL    |                |
| dt                | datetime              | NO   | PRI | NULL    |                |
+-------------------+-----------------------+------+-----+---------+----------------+
7 rows in set (0.01 sec)


mysql> describe points_log;
+-------------------+-----------------------+------+-----+---------+----------------+
| Field             | Type                  | Null | Key | Default | Extra          |
+-------------------+-----------------------+------+-----+---------+----------------+
| id                | mediumint(8) unsigned | NO   | PRI | NULL    | auto_increment |
| nick              | char(25)              | NO   | PRI | NULL    |                |
| amount            | decimal(10,4)         | YES  | MUL | NULL    |                |
| stream_online     | tinyint(1)            | NO   | MUL | NULL    |                |
| modification_type | tinyint(3) unsigned   | NO   | MUL | NULL    |                |
| dt                | datetime              | NO   | PRI | NULL    |                |
+-------------------+-----------------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)

I would get rid of "WHERE [...] NOT IN", rather use "LEFT JOIN [...] WHERE [...] ISNULL" because it's much faster — valicu2000
– valicu2000, Commented Jan 4, 2015 at 17:46
@valicu2000 Can you explain? I'm not sure how to craft that statement. — Preston Connors
– Preston Connors, Commented Jan 4, 2015 at 17:55
Something like: INSERT INTO achievements ( nick, cost, achievement_type, announced_in_chat, shown_on_stream, dt ) ( SELECT p.nick, 2000, 0, TRUE, FALSE, NOW() FROM points_log p LEFT JOIN achievements a ON p.nick=a.nick WHERE a.nick ISNULL AND a.achievement_type = 0 AND a.stream_online = TRUE ) GROUP BY nick HAVING SUM(amount) >= 2000; — valicu2000
– valicu2000, Commented Jan 4, 2015 at 17:59

Gordon Linoff · Accepted Answer · 2015-01-04 18:00:40Z

Optimizing the insert is really just optimizing the query:

SELECT nick, 2000, 0, TRUE, FALSE, NOW() 
FROM points_log
WHERE nick NOT IN (SELECT nick 
                   from achievements
                   WHERE achievement_type = 0 AND stream_online = TRUE
                  )
GROUP BY nick
HAVING SUM(amount) >= 2000;

I might turn this query "inside out":

SELECT pl.nick, 2000, 0, TRUE, FALSE, NOW() 
FROM points_log pl LEFT JOIN
     achievements a
     ON a.nick = pl.nick AND a.achievement_type = 0 AND a.stream_online = TRUE
WHERE a.nick IS NULL
GROUP BY nick
HAVING SUM(amount) >= 2000;

For this query, you want an index on achievements(nick, achievement_type, stream_online.

You claim in the question that you simply want to avoid achievements for nick in achievements. The easiest way to do that is with a query:

INSERT INTO achievements(nick, cost, achievement_type, announced_in_chat, shown_on_stream, dt)
    SELECT pl.nick, 2000, 0, TRUE, FALSE, NOW() 
    FROM points_log pl 
    GROUP BY nick
    HAVING SUM(amount) >= 2000
    ON DUPLICATE KEY SET nick = VALUES(nick);

And a unique index:

CREATE UNIQUE INDEX idx_achievements_nick ON achievements(nick);

However, your query has additional conditions that your question doesn't address.

Collectives™ on Stack Overflow

MySQL Optimize Nested SELECT and INSERT Statement

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related