In fact, performance has improved since you rewritten it asINNER JOIN.

The cost of the first query is:

mysql> select film_id, description from film order by title limit 50,5; mysql> show status like 'Last_query_cost'; +-----------------+------------+ | Variable_name | Value | +-----------------+------------+ | Last_query_cost | 209.799000 | +-----------------+------------+

The cost of the second query is:

mysql> select a.film_id, a.description from film a inner join (select film_id from film c order by title limit 50,5) b on a.film_id = b.film_id; mysql> show status like 'Last_query_cost'; +-----------------+----------+ | Variable_name | Value | +-----------------+----------+ | Last_query_cost | 0.000000 | +-----------------+----------+

So your goal has been achieved.

The reason for the improvement is that after usingINNER JOIN, the covering index comes into play. The entire matching process only requires film_id and title, and they both have indexes.descriptionThe field will only be read after matching, so a lot of IO is saved.

Let’s briefly analyze the second query.

The JOIN algorithm implemented by MySQL is essentially a simple nested loop. The outermost loop always traverses linearly and does not use indexes (this is a temporary table and there is no index). If the inner loop has an index, it will Use indexes. Therefore, the size of the outer loop directly determines the performance of JOIN.

Judging from the results of the explain, MySQL places the tableSELECTobtained by thebclause in the outer loop. This table only has 55 rows of records, so MySQL decides to start with it first, and the access type isALL, which means scanning the entire table, that is, 55 rows of records.

*************************** 1. row *************************** id: 1 select_type: PRIMARY table:  type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 55 Extra: NULL

The second level of loop is based on the tablea. Here we only need to findfilm_id, using the primary key index, which is very fast. Remember, the third level of looping occurs only after matching.

*************************** 2. row *************************** id: 1 select_type: PRIMARY table: a type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 2 ref: b.film_id rows: 1 Extra: NULL

The third level loop is based on the tableSELECTin thefilmclause, matchingtitle, and also uses an index, which is very fast.

*************************** 3. row *************************** id: 2 select_type: DERIVED table: c type: index possible_keys: NULL key: idx_title key_len: 767 ref: NULL rows: 989 Extra: Using index

You ask why the entire table is still scanned? In fact, it shouldn't be. If you want to scan the entire table, the access type should be ALL instead of index. So don't worry.

Your optimized writing method happened to appear in Chapter 6 ofHigh Performance MySQL, I happened to see it.

The usage of INNER JOIN is to take out the tuples that meet the requirements, and you must scan the entire table.
During the query with id 2. . . Why didn’t you add and a.film_id=2;

What’s the use of writing like this?