The DBA sends a slow query problem on the line, and SQL is as follows (omitting some of the highlights for highlighting):
SELECT DISTINCT Article0_.id, etc. fields from Article_table article0_, hits_table articlehit1_ where article0_.id=articlehit1_. ID order by hits;
Explain result: time consuming 4.03S
unexpectedly, there will be a using temporary, order by only use a table, under normal circumstances will not appear with the aid of the table and then sort the case (this situation is that multiple tables are related to the sort field will be caused), The normal situation should be only after sorting the tables that have a relationship and then making the connection operation (for example, inner join in this example). index What is actually built, see the Type field can be seen, can refer to MySQL tuning----left JOIN
a serious look at the SQL statement, unexpectedly in the primary key ID added distinct, this is not wasted performance, try to modify the statement as:
Select Article0_.id, etc field from Article_table article0_, hits_table articlehit1_ where article0_.id=articlehit1_.id order b Y hits;
Explain result: time consuming 0.04S
unexpectedly because of a distinct modification, let MySQL think two table sort need to use temporary table, is really a wonderful problem. Distinct is very time-consuming and ready to be covered in the next blog post.
Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.
MySQL Tuning--using Temporary
