Interesting questions about MySQL limit and order

MySQL limit and order by have interesting problems. I believe everyone knows that MySQL's limit syntax select/delete/update... limit start, len; Is to query or update several pieces of data. Of course, we need to know that if there are limit update statements, we should have the order by primary key/unique key syntax for replication security, otherwise the updated row will be uncertain. What is my scenario? The query of an SQL statement is particularly slow: select matchid, uid from usermatch_create where status = 'nostart' and matchid % 2 = 0 order by matchid asc limit 1; I have read the execution plan and it is normal: explain select matchid, uid from usermatch_create where status = 'nostart' and matchid % 2 = 0 order by matchid asc limit 1; + ---- + ------------- + ------------------ + ------- + ---------------- + --------- + ------ + ------------- + | id | select_type | Table | type | possible_keys | key | key_len | ref | rows | Extra | + ---- + ------------- + rows + ------- + ---------------- + --------- + ------ + ------------- + | 1 | SIMPLE | usermatch_create | index | idx_status_add | PRIMARY | 8 | NULL | 6 | Using where | + ---- + ------------- + signature + ------- + ---------------- + --------- + ------ + --------------- + but the execution is indeed equivalent. And then set profiling = on, then we can see that the longest time-consuming phase is sending data. This shows that the data scanned during this query is extremely large, resulting in a large amount of data transmission. Why? Isn't it the optimizer's estimation? Does it Scan six rows? (of course, this value is an estimate, which is not completely accurate )? Then, change order by matchid asc to order by matchid desc in an attempt. Then, execute the query again, which is a second kill. The execution plan is exactly the same as before. I was wondering if there is a difference between the two order by queries by B-tree? I would like to think that the principle is the same. ascending and descending order are only different scanning directions. Next, we try to remove the status condition in the where condition and find that the two statements are executed at the same speed. At this point, we can see that the problem lies in the status judgment. The guess is that the order by asc query is particularly slow due to data distribution problems. At that time, it was estimated that when order by matchid asc was the condition, because the status of a large number of rows does not meet the 'nostart' condition, it is equivalent to a white scan of many rows. If order by matchid desc is used, the row with status = 'nostart' will be found in the first few rows, so the speed will be very fast. Verification Method: select status from usermatch_create where matchid % 2 = 0 order by matchid asc limit 10000; the above result set shows that the first 10000 rows are not 'nostart' select status from usermatch_create where matchid % 2 = 0 order by matchid asc limit 10000; the above result set shows that the status in the first 10 is 'nostart'. I believe that the reason for the large difference in the query time between the two order by statements is clear. So what is the cause of this phenomenon? Why has this query been slow before? This week, due to machine reasons, this business is missing a slave database. To reduce the pressure, I want my business to stop daily data deletion tasks, this Delete task will delete many statuses that are not 'nostart'. Therefore, deleting these statuses will not lead to so many useless scans. So far, the problem has been well solved. It can only be said that this problem is very interesting.