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.

Answer 1

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