To reasonably sort the fields in a MySQL federated index

In the MySQL where condition, there are sometimes a lot of conditions, usually in order to speed up the field will be placed in the federated index, you can quickly improve the search speed;

However, a reasonable ordering of the field order in the federated index can improve the speed

Example: SELECT * FROM table where (groupid=1000) and (userid=500) and (time=140012345)

The established indexes are also usually very random, based on the order of the fields in the Where condition

ALTER table Table ADD INDEX g_u_time_index (groupid,userid,time);

Then MySQL will first retrieve all the data of groupid=1000, if found 1000, and then in the retrieved 1000 data to retrieve userid=500 data, assuming that there are 300 remaining, and then in 300 to search for time=140012345 data, Suppose there are 100 left, so that a total of search for 1000+300+100=1400 data to find the matching 100

But for better optimization, we now need to calculate the number of fields before indexing and calculate their proportions in the table.

You can write a SELECT statement to view: Calculate the proportion of each condition in the table

Select COUNT (*) total,sum (groupid=1000) gtotal,sum (userid=500) utotal,sum (time=1400123456) time from table;

Total 10000

Gtotal 1000

Utotal 300

Time 800

Visible if the first search Island userid=500 data can be removed more than half, only 300 matches, and then search in 300 in accordance with time=1400123456 and groupid=1000 data, so greatly reduced the number of queries;

In order from small to large, you can reduce the number of searches

Change SQL to: SELECT * FROM table where (userid=500) and (time=140012345) and (groupid=1000)

ALTER table Table ADD INDEX g_u_time_index (userid, Time, GroupID)

Reference: High performance MySQL chapter fifth creating high Performance indexes

To reasonably sort the fields in a MySQL federated index