/*SELECT Count (*) from (*/EXPLAINSELECTSt.id,st. Stu_name,tmpgt.time,tmpgt.goutong fromJingjie_students St Right JOIN (SELECT * from_goutong Gttime, (
SELECTname_id NameID,Max(time) time fromT_goutongGROUP byNAME_ID) GT
WHEREgttime.name_id=Gt.nameid andGttime.time=Gt.time andGttime.time>'2015-07-19 16:18:02') TMPGT onSt.id=tmpgt.name_id; /*) student_latested;*/
When a federated index is not used, the 'GROUP by name_id' query uses a full table scan,
Index name_id (name_id, time) wasused after using the Federated Index: note the order in which the Federated Index is established
Familiarity with GROUP by using indexes:
I. Index application of GROUP by
1. The query field must be consistent with the following group by
Select Teamid from Competeinfo where Teamid >10 GROUP by Teamid.
Here is the search by Teamid. Complete GROUP BY.
2, the application of Joint index, remember to note that the group by order, where condition and the group by field is an index inside the
This table Competeid,teamid build a federated index
1) Select Teamid from Competeinfo where Teamid >10 and Competeid > 100020 GROUP by Competeid
This query uses the Competeid,teamid Federated index.
2) Select Teamid from Competeinfo where Teamid >10 and Competeid > 100020 GROUP by Teamid
In this case, the index is not used in query group by.
Second, the following is a summary of the use of the federated Index
Index (name,age) indicates that a federated index is established on the Name,age two column
If the Where name= ' PP ' can use the index
Where age=25 cannot use the index
Where Name= ' pp ' and age>25 can use the index
where name = ' PP ' order by age can use index
Where name> ' pp ' ORDER by age cannot use index
Where name> ' pp ' ORDER by Name,age can use the index
ORDER BY name ASC Age DESC will not be able to use the index!
MySQL optimizes group by queries by using a full index