Adjustment of a composite index

The following table is used to adjust a composite index: | Field | Type | Null | Key | Default | Extra | + ------------- + ----------------------- + ------ + ----- + --------- + ------------------ + | pid | int (10) unsigned | NO | PRI | NULL | auto_increment | tid | mediumint (8) unsigned | NO | MUL | 0 | showtime | int (11) | YES | 0 | www.2cto.com has a data volume of more than 200 million. A composite index was originally created on tid and showtime. Existing sqlSELECT * FROM test where and tid = '20160301' AND pid> = 47992 and showtime> 0 order by p. showtime, p. pid LIMIT 0, 40; so the INDEX is modified as follows (tid cannot be changed to the leftmost prefix, tid + showtime is used for other SQL statements): ALTER TABLE test ADD INDEX idx_tid_showtime_pid (tid, showtime, pid); SQL Execution efficiency is very low, please see explain www.2cto.com + ---- + ------------- + ---------------- + ------- + certificate + -------------------- + --------- + ------ + -------- + --------------- + | id | se Lect_type | table | type | possible_keys | key | key_len | ref | rows | Extra | + ---- + ------------- + -------------- + ------- + upper + --------- + ------ + --------------- + | 1 | SIMPLE | test | range | idx_tid_showtime_pid | 8 | NULL | 123444 | Using where | + ---- + --------------- + -------------- + ------- + ------------- --------- + ------ + -------- + ------------- + The efficiency is still low. The execution time is more than 0.7 seconds through the slow-log record. After the showtime condition is removed, the efficiency of using only the tid and pid composite indexes is very high, so the INDEX is modified again: alter table test add index idx_tid_pid_showtime (tid, pid, showtime ); this greatly improves the efficiency and reduces the execution time to less than 0.1. Www.2cto.com + ---- + ------------- + ------- + hour + --------- + ------ + hour + | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | + ---- + ------------- + ------- + -------------------------------------------------------------- Hour + ---------------------- + --------- + ------ + hour + | 1 | SIMPLE | test | range | idx_tid_showtime_pid, idx_tid_pid_showtime | 12 | NULL | 3290 | Using where; using filesort | + ---- + ------------- + ------- + response + -------------------- + ----- ---- + ------ As mentioned above, I personally understand this. The idx_tid_showtime_pid (tid, showtime, pid) index satisfies the conditions for using the index order, therefore, 123444 rows of data need to be scanned. The column order of the index idx_tid_pid_showtime (tid, pid, showtime) is different from that of the order by. The index cannot be used for sorting, but the efficiency of using the index in the where condition is good, only 3290 rows of data are scanned, even when sorting is added. It can be seen that the addition of indexes cannot simply adapt to the sorting order by field order. Although the time spent in sorting is omitted, the other overhead is far greater than the effect of sorting a small amount of data.