How to consider an order by query when MySQL creates an index

When you index a table, some of the queries will use the order by, the group by, and so on, and in a lower version (with a prior version of ICP), you may experience a situation where the query uses the index, so the order by cannot use the index, and of course we can create the Federated index, So under what circumstances is it valid to create a federated index? You can see by explain whether there is a file sort,where using the index.

So how do I avoid the file sort, and then how to create the index, here to summarize: 1, according to the index query data, whether you need to sort, if necessary will produce file sort, otherwise will not; So when you create an index, you need to pay special attention to avoid file Sort as much as possible by creating an index to resolve, can not be resolved to change the SQL, or consider other options. However, you need to consider that file sort is not necessarily slow, it depends on the amount of data, in some cases the file sort is allowed, and the file sort is divided into memory and disk, which can be sorted on disk only if the sort buffer is not loaded.

2, the Union index uses the length to need to notice, see the Union Index to use several columns, what kind of SQL will use the degree of index. For example, a for the scope of query,<, >,!=, then B can not be used to (do not support ICP). Subsequent columns are only used when the current prefix is listed as =, <=, or >=.

The following are federated Index Idx_a_b_c (A,B,C) Whether there is a file sort under various query conditions, and the index used in addition. (MySQL version, 5.1.63)

Thinking:

CREATE TABLE TTT (
ID Int (a) not NULL auto_increment,
UID Int (a) not NULL,
Status tinyint (2) not NULL,
Ordertime Int (one) not NULL,
View varchar not NULL
Primary key (ID),
Key Idx_status_rodertime (Status,ordertime),
Key Idx_uid (UID)) engine = InnoDB;
) engine = InnoDB;
This table has more than 3,000 W. where the UID and status,ordertime distinct values.

Mysql> Select COUNT (Distinct uid) from TTT;
+------------------------+
| COUNT (distinct UID) |
+------------------------+
| 9112 |
+------------------------+
1 row in Set (51.89 sec)

Mysql> Select COUNT (Distinct status,ordertime) from TTT;
+--------------------------------+
| COUNT (distinct status,ordertime) |
+--------------------------------+
| 5424 |
+--------------------------------+1 row in Set (51.77 sec)
1 row in Set (51.77 sec)
The previous table has one such query:

SELECT * from TTT where status=6 and Ordertime >=1451012466 and ordertime <=1451016066 ORDER by uid ASC limit 100;
In this case, how to optimize this SQL, why? (not limited to database version)