MySQL的查詢最佳化很傻

select id from message where author_id in (select followed_id from Relation where follower_id=1) order by id desc

 

和

分別執行

$userlist = select followed_id from Relation where follower_id=1

再

select id from message where author_id in ($userlist) order by id desc

 

竟然有兩個數量級的差別，DAMN IT!!!!

 

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

MySQL sometimes optimizes subqueries very badly. The worst offenders are IN( )
subqueries in the WHERE clause. As an example, let’s find all films in the Sakila sample
database’s sakila.film table whose casts include the actress Penelope Guiness
(actor_id=1). This feels natural to write with a subquery, as follows:
mysql> SELECT * FROM sakila.film
    -> WHERE film_id IN(
    ->    SELECT film_id FROM sakila.film_actor WHERE actor_id = 1);
It’s tempting to think that MySQL will execute this query from the inside out, by
finding a list of actor_id values and substituting them into the IN( ) list. We said an
IN( ) list is generally very fast, so you might expect the query to be optimized to
something like this:
-- SELECT GROUP_CONCAT(film_id) FROM sakila.film_actor WHERE actor_id = 1;
-- Result: 1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980
SELECT * FROM sakila.film
WHERE film_id
IN(1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980);
Unfortunately, exactly the opposite happens. MySQL tries to “help” the subquery by
pushing a correlation into it from the outer table, which it thinks will let the sub-
query find rows more efficiently. It rewrites the query as follows:
SELECT * FROM sakila.film
WHERE EXISTS (
   SELECT * FROM sakila.film_actor WHERE actor_id = 1
AND film_actor.film_id = film.film_id);
Now the subquery requires the film_id from the outer film table and can’t be exe-
cuted first. EXPLAIN shows the result as DEPENDENT SUBQUERY (you can use EXPLAIN
EXTENDED to see exactly how the query is rewritten):

《High Performance MySQL》 Page 204