MySQL Using Temporary; Using filesort INNER Join optimization

Problem

The "show full processlist" statement makes it easy to find the problem SQL, as follows:

SELECT post.*from postinner JOIN post_tag on post.id = Post_tag.post_idwhere Post.status = 1 and post_tag.tag_id = 123ORDE R by post.created Desclimit 100

Note: Because post and tag are many-to-many relationships, there is an association table Post_tag.

Try using explain to query the SQL Execution plan (limited in length, with the result being truncated):

+----------+---------+-------+-----------------------------+| Table    | key     | rows  | Extra                       |+----------+---------+-------+-----------------------------+| post_tag | tag_id  | 71220 | Using where; Using Filesort | | Post     | PRIMARY |     1 | Using where                 |+----------+---------+-------+-----------------------------+

The following gives the optimized SQL, the only change is to change the connection mode to "straight_join":

SELECT post.*from poststraight_join post_tag on post.id = Post_tag.post_idwhere Post.status = 1 and post_tag.tag_id = 123O Rder by post.created Desclimit 100

Try using explain to query the SQL Execution plan (limited in length, with the result being truncated):

+----------+----------------+--------+-------------+| Table    | key            | rows   | Extra       |+----------+----------------+--------+-------------+| post     | status_created | 119340 | Using where | | Post_tag | post_id        |      1 | Using where |+----------+----------------+--------+-------------+

Compared to two times before and after the optimization of explain results, the optimized SQL although "rows" larger, but no "using filesort", overall, performance is still improved.

Reminder: note the order in which the tables appear in the explain results two times and will be explained later.

Explain

For the first SQL, why did the MySQL optimizer choose a time-consuming execution scenario? For the second SQL, why did you improve performance after changing the connection mode to Straight_join?

It's all going to have to do with how MySQL handles multi-table connections, first the MySQL optimizer determines who is the driver table, which is the table, and when dealing with such problems, the MySQL optimizer uses a simple and rude workaround: Which table has a small result set, which table is the driver table, Of course, the MySQL optimizer actually handles a lot more complex, for reference: How the MySQL optimizer chooses the index and join order.

Note: In the explain result, the table that appears in the first row is the driver table.

To continue the Post connection Post_tag example, the MySQL optimizer has the following two choices, namely:

• With post as driver table, filtered by status_created index, result set 119340 rows
• Post_tag as driver table, filtered by tag_id index, result set 71220 rows

Obviously, the result set of the Post_tag filter is smaller, so the MySQL optimizer chooses it as the driver table, sadly, we also need to sort the created field in the post table, that is, the sort field is not in the driver table, so inevitably there is a "using filesort", even"using temporary".

Knowing the ins and outs, it is easy to optimize, as far as possible to ensure that the sort field in the driver table, so you must use the post as the driver table, so must use "straight_join" forced connection order.

In fact, in some special cases, the sort field can not be in the driver table, such as the driver tables result set only one row of records, and when connecting to other tables, the index in addition to the connection field, but also contains the sort field, when the table is joined, the index of the data itself is naturally well sequenced.

Now that we're talking here, incidentally, humorous, you may encounter a problem similar to the following: Originally well-run query statements, after a period of time, may suddenly become very bad. One of the most likely reasons is that the data distribution has changed, resulting in the MySQL optimizer's choice of the driver table has changed, resulting in index failure situation, so it is better to find out more, pay attention to these situations.

MySQL Using Temporary; Using filesort INNER Join optimization