MySQL Chokes on IS NULL 11,000 records

Question

I found a similar question that was resolved with indexes but I found no changes to query speed, about 80 seconds to return the games section. This is pulling meta values from a WordPress database into a temp table to compare if the updated fame timestamp either matched or does not exist in a published post via the post meta values. I found that g.game_updated != m.meta_value does not show when m.meta_value is NULL, without adding the OR IS NULL query is sub .5 seconds, adding makes this 80 seconds plus with 10,800 records. Adding the primary and regular index on the two values makes no impact

CREATE TEMPORARY TABLE tmp_meta
        SELECT distinct m.meta_value as game_id,m2.meta_value FROM wp_postmeta m
        INNER JOIN wp_postmeta m2
            ON m.post_id = m2.post_id
            AND m2.meta_key = 'game_updated'
            AND m.meta_key = 'game_id';

ALTER TABLE tmp_meta ADD PRIMARY KEY (game_id(100));
ALTER TABLE tmp_meta ADD KEY (meta_value(100));

CREATE TEMPORARY TABLE tmp_needsUpdate         
SELECT g.*,m.meta_value FROM wp_radium_games g 
    LEFT JOIN tmp_meta m 
    on m.game_id = g.game_id
    WHERE ( g.game_updated !=  m.meta_value OR m.meta_value IS NULL);

Removed last query as it is not relevant to the discussion on why adding m.meta_value IS NULL adds 80 seconds to the query.

+---------+--------------+------------+
| post_id |   meta_key   | meta_value |
+---------+--------------+------------+
|       1 | game_id      |        100 |
|       1 | game_updated |       9999 |
|       2 | game_id      |        101 |
|       2 | game_updated |       9997 |
|       3 | game_id      |        102 |
|       3 | game_updated |       9992 |
+---------+--------------+------------+

+---------+--------------+-----------+
| game_id | game_updated | game_name |
+---------+--------------+-----------+
|     100 |         9999 | game1     |
|     101 |         9999 | game2     |
|     102 |         9992 | game3     |
|     104 |         9992 | game4     |
|     105 |         3333 | game5     |
|     106 |         3333 | game6     |
+---------+--------------+-----------+

This should return Games 101, 104, 105 and 106 as 100 matched update 9999 as well as 102 matched 9992. game 101 did not match game_updates and 104 - 106 will have null values.

`LEFT JOIN... IS NOT NULL`? Isn't that the same as `JOIN...` — Strawberry, Nov 29 '20 at 08:04
If you mean the tmp_needsUpdated it is needed as we should have more games than meta values if we need to create a new post. — Radium Chris, Nov 29 '20 at 15:14
Help me understand why g.game_updated != m.meta_value is the join needed? why !=? Sample data would help — xQbert, Dec 01 '20 at 16:22
I have not figured out how to add data here yet, but the reason is that I have the table of games, and it has an update timestamp from when games are added or edited. This is brought over to the WordPress site and a cron function creates the temp_meta table to get all the games currently as created posts. If the timestamps do not match game_updated on both then the game post needs updated, if the meta values do not exists then the post has not been created yet hence the NULL. — Radium Chris, Dec 01 '20 at 16:34
Can you share the execution plan for the long-running query? — Nico Haase, Dec 01 '20 at 21:28
@Nico Haase Really don't need an execution plan as first query builds a temp table, that is fast not an issue so we can assume that already exists, then its the issue of why the is null WHERE kills the query. — Radium Chris, Dec 01 '20 at 21:35

score 0 · Answer 1 · answered Jan 26 '21 at 19:38

I think this will speed up the query:

    LEFT JOIN tmp_meta m 
       ON m.game_id = g.game_id
       AND g.game_updated !=  m.meta_value
    WHERE m.meta_value IS NULL;

ON is used for saying how the tables are related; WHERE is for filtering. But the pattern LEFT JOIN...IS NULL is testing whether the LEFT JOIN found a matching row.

Another approach is to replace the LEFT JOIN with WHERE ... AND ( EXISTS SELECT 1 ... )

Also beneficial for performance are the generic index improvements for post_meta: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta

MySQL Chokes on IS NULL 11,000 records

1 Answers1