New answers tagged explain
The accepted answer overlooks the concept of covering indexes, and also does not mention the importance of indexes on multiple columns, together in one index. A single index over both columns in the WHERE clause: ALTER TABLE clients ADD KEY(source,added) -- adding this ALTER TABLE clients ADD KEY(added,source) -- or this ...will usually help you more ...
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;
I resolved the problem by dropping and recreating IX1. (The reasoning behind that was to invalidate any cached plan still using it.) The query now returns 420 rows after 11 seconds. If anybody can give me a good theory on what happened I will accept it as a correct answer.
Top 50 recent answers are included