About MySQL Join

Recently has been plagued by the work of MySQL join problem, the company does not recommend the use of multi-table query, so I explore how join is working

The answer is NO!

Collection

In other words, the table query says that the field of order by group must be the driver table.

I understand: The LEFT JOIN table is the driver table, right join table is the driver table, inner join see explain first behavior driver table

Background:

Users table 866 row data PRIMARY key index

User_city 35 row Data primary key index

1.explain Select Users.id from user_city left join users on users.id=user_city. ' user_id '

Driver Table user_city user_id No index, users.id primary key index

2.explain Select Users.id from user_city left join users on users.id=user_city. ' ID '

Driver table User_city ID is primary key index, Users.id primary key index

3.explain Select Users.id from user_city left join users on users.user_id=user_city. ' ID '

Driver table User_city ID is primary key index, users.user_id no index

3.explain Select Users.id from user_city left join users on users.user_id=user_city. ' ID ' where user_city.id<40

Driver table User_city ID is the primary key index, USERS.USER_ID does not have an index where Condition ID index

4.explain Select Users.id from user_city left join users on users.user_id=user_city. ' ID ' where user_city.user_id<1700

Driver table User_city ID is primary key index, users.user_id no index where condition user_id not indexed

5. These two comparisons are very important, when the table is sorted, the order in addition to the field must be the driver table and the index, the filter on must also be used to the index can be used, are all use!

Explain select Users.id from user_city left joins users on users.id=user_city. ' ID ' ORDER BY users.id Desc

are used in the index, but the list is not the Driver table field, there will definitely be a using temporary; Using Filesort

6.explain Select Users.id from user_city left join users on users.id=user_city. ' ID ' ORDER BY user_city.id Desc

are used to index, the order of the Driver table field, will not appear using temporary; Using Filesort

7.explain Select Users.id from user_city left join users on users.user_id=user_city. ' ID ' ORDER BY user_city.id Desc

USER_ID does not have an index, even if the by field is a driver table segment

Add a little

It is generally possible to join with a join, because, in this case, MySQL will determine the appropriate driver table.

About MySQL Join