Identical query, tables, but different EXPLAIN and performance

Question

I have two tables levels and users_favorites

+--------------------+--------------+------+-----+---------+-------+
| Field              | Type         | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------+-------+
| id                 | int(9)       | NO   | PRI | NULL    |       |
| user_id            | int(10)      | NO   | MUL | NULL    |       |
| level_name         | varchar(20)  | NO   |     | NULL    |       |
| user_name          | varchar(45)  | NO   |     | NULL    |       |
| rating             | decimal(3,2) | NO   |     | 2.50    |       |
| votes              | int(5)       | NO   |     | 0       |       |
| plays              | int(5)       | NO   |     | 0       |       |
| date_published     | date         | NO   | MUL | NULL    |       |
| user_comment       | varchar(255) | YES  |     | NULL    |       |
| playable_character | int(2)       | NO   | MUL | 1       |       |
| is_featured        | tinyint(1)   | NO   | MUL | 0       |       |
+--------------------+--------------+------+-----+---------+-------+

+----------+--------+------+-----+---------+-------+
| Field    | Type   | Null | Key | Default | Extra |
+----------+--------+------+-----+---------+-------+
| user_id  | int(8) | NO   | PRI | NULL    |       |
| level_id | int(8) | NO   | PRI | NULL    |       |
+----------+--------+------+-----+---------+-------+

I have my local dev environment and the production servers. This query:

SELECT id, level_name, date_published, rating
FROM levels
WHERE id IN (SELECT level_id FROM users_favorites WHERE user_id = 2);

runs very fast locally (around 0.0x seconds) and very slow on production (~15 seconds). The EXPLAIN's are different. On local:

id  select_type table   type    possible_keys   key key_len ref rows Extra
1   SIMPLE  users_favorites ref uniq_user_level,idx_user    idx_user    4   const   21  "Using index"
1   SIMPLE  levels  eq_ref  PRIMARY PRIMARY 4   users_favorites.level_id    1   "Using where"

And on production:

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   PRIMARY levels  ALL NULL    NULL    NULL    NULL    3368988 "Using where"
2   "DEPENDENT SUBQUERY"    users_favorites eq_ref  uniq_user_level,idx_user    uniq_user_level 8   const,func  1   "Using index"

I know the data is the the same because it was imported and exported from the same schema. I've run OPTIMIZE and made sure the indexes are the same, tried forcing the indexes. Nothing worked.

The only difference I can spot is the version of MySQL: locally it's 5.6.10, on production it's 5.5.34-log. If that's the reason, I'll upgrade, but I'm wondering if there could some other reason? Or way to phrase the query so it always reduces by the subquery first, as it does locally: 21 rows instead of 3368988?

TIA

Answer 1

Just make a simple join. Sub-queries does not provide the best result quite often

EXPLAIN SELECT l.id, l.level_name, l.date_published, l.rating
FROM levels AS l
INNER JOIN users_favorites AS uf 
ON uf.level_id = l.id
WHERE l.user_id = 2;