MySQL slow query of the same field do two times to sort of thinking

The online business uses the ORM framework, and discovers a slow query SQL that has been sorted two times in the same field, resulting in filesort. The
ORM Framework has its own table structure as follows:

CREATE TABLE ' Unopen ' (
  ' Corp ' tinyint (3) unsigned not NULL DEFAULT ' 0 ',
  ' invtype ' tinyint (3) not Null default ' 1 ',
  ' uid ' int (a) NOT null default ' 0 ',
  ' username ' char (+) NOT null default ' 0 ',
  ' Fatid ' int (a) NOT null default ' 0 ',
  ' Fatname ' char (h) NOT null default ' 0 ',
  ' invtitle ' ch AR (m) not null default ',
  ' Invest ' decimal (12,2) NOT null default ' 0.00 ',
  ' opened ' Decimal (12,2) unsigned NOT NULL default ' 0.00 ',
  ' unopened ' decimal (12,2) NOT null default ' 0.00 ',
  ' doflag ' tinyint (3) NOT null default ' 0 ',
  ' manual_opened ' decimal (12,2) NOT null default ' 0.00 ',
  PRIMARY KEY (' uid ', ' Corp ', ' Doflag ', ' Invtype ')
) Engine=innodb DEFAULT Charset=utf8;
See this table structure will think of the first thing you want to increase the primary key (a bit obsessive feeling), in fact, the table does not necessarily need a self-add the field to master key, as long as it is able to ensure that the only field can be used to master the key, preferably integral type. But the main need to see the application scene, it may be better to have only the columns of the equivalent query, or multiple columns, to create a single-column primary key or a federated primary key, because if a two-level index exists, it may also cause a return table query, which is directly a primary key that can be used to fetch data directly from the primary key. Back to the point, the original SQL is as follows:

SELECT ' i '. *, ' V ', ' type ', ' V ', ' status ' from ' V_stat '. ' Unopen ' as ' I '
Left JOIN ' v_user '. ' Info ' as ' V ' on ' i '. ' uid ' = ' V '. ' uid ' WHERE (' i '. ' uid ' > 0) and (' I '. ') ' and ' (' I ') ' and ' (' I ') ' Dofla G ' = ' 2 ') Order BY ' UID ' ASC, ' UID ' ASC, ' Corp ' ASC LIMIT 50;
This SQL execution takes more than 2.5s, explain a look at the Filesort

+----+-------------+-------+-------+---------------+---------+---------+---------------+---------+------------- ----------------+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+---------------+---------+------------- ----------------+
| 1 | Simple | I | Range | PRIMARY | PRIMARY | 4 | NULL | 1610297 | The Using where; Using Filesort |
| 1 | Simple | V | Ref | PRIMARY | PRIMARY | 4 |       V_stat.i.uid |                             1 | |
+----+-------------+-------+-------+---------------+---------+---------+---------------+---------+------------- ----------------+
Depending on the structure of the table and the SQL, indexed field query sorting is used, and the sorted fields are base table segments, and you should not need to sort them, and how to let group by do not produce sort can refer to the order by query when creating an index under MySQL. When you adjust single quotes, you find that the SQL has a two-order UID, and it doesn't explicitly indicate which table it is. The meaning should be sorted using I.uid and v.uid, so I added.

Mysql> explain SELECT ' i '. *, ' V '. ' type ', ' V '. ' Status ' from ' V_stat '. ' Unopen ' as ' I ', ' v_user '. ' Info ' as ' V ' On ' i '. ' uid ' = ' V '. ' uid ' WHERE (' i '. ' uid ' > 0) and (' I '. ' Corp ' = 3) and (' I '. ' Doflag ' = 2) Order by I. ' UID ' ASC, v. ' UID ' ASC, I. ' CORP ' ASC LIMIT 50;
+----+-------------+-------+-------+---------------+---------+---------+---------------+---------+------------- ---------------------------------+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+---------------+---------+------------- ---------------------------------+
| 1 | Simple | I | Range | PRIMARY | PRIMARY | 4 | NULL | 1610297 | The Using where; Using temporary; Using Filesort |
| 1 | Simple | V | Ref | PRIMARY | PRIMARY | 4 |       V_stat.i.uid |                                              1 | |
+----+-------------+-------+-------+---------------+---------+---------+---------------+---------+------------- ---------------------------------+
A using temporary was found and the UID of the I table was left by default when no sort table was specified. It's already obvious here that the UID does not need to be sorted two times, because the UID of the two tables is the same, and the order of the retrieved time is definitely the same. Decisively delete the following UID sort and explicitly sort the table for I, here only to increase readability.

Mysql> explain SELECT ' i '. *, ' V '. ' type ', ' V '. ' Status ' from ' V_stat '. ' Unopen ' as ' I ', ' v_user '. ' Info ' as ' V ' On ' i '. ' uid ' = ' V '. ' uid ' WHERE (' i '. ' uid ' > 0) and (' I '. ' Corp ' = 3) and (' I '. ' Doflag ' = 2) Order by I. ' UID ' ASC, i. ' Cor P ' ASC LIMIT 50;
+----+-------------+-------+-------+---------------+---------+---------+---------------+---------+------------- +
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+---------------+---------+------------- +
| 1 | Simple | I | Range | PRIMARY | PRIMARY | 4 | NULL | 1610297 | Using where |
| 1 | Simple | V | Ref | PRIMARY | PRIMARY | 4 |       V_stat.i.uid |             1 | |
+----+-------------+-------+-------+---------------+---------+---------+---------------+---------+------------- +
Filesort disappears, the query changes directly to MS level.