The query optimization of MySQL is silly.

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

 

And

Run

$ Userlist = select followed_id from Relation where follower_id = 1

Again

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

 

There are actually two orders of magnitude differences, 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,
Finding a list of actor_id values and substituting them into the in () list. We said
In () List is generally very fast, so you might should CT the query to be optimized
Something like this:
-- Select group_concat (film_id) from sakila. film_actor where actor_id = 1;
-- Result: 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 (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
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