I'm just wondering, if I do SELECT COUNT(*) FROM ... WHERE ... ORDER BY ..., does MySQL sort the records before counting? Or does it understand that it makes no sense in this case and just ignores ORDER BY?
CodePudding user response：
The ORDER BY statement, is (in this query) the last statement to be executed.
The ORDER BY statement only afects the result, never affects the data you are looking into, so in this case the system will work in this way:
- Count the rows that meet your condition (maybe using an index or a full scan, depending on the data and the conditions).
- Get the rows for your answer, in this case just 1 row.
- Try to order the rows of your answer, in this case with no effect because you have only one row.
So the order by (in this case) will have no effect, because the order applies only to the rows of the answer and you only have 1 row. And as someone said, it doesn´t make any sense to try to order a result with just one row.
CodePudding user response：
I try to answer your question with what I know, but I'm not sure if it will help you, if my answer is wrong, I hope you can point it out.
I am using the InnoDB engine of mysql. InnoDB cannot maintain a row_count variable like MyISAM, so when performing the count(*) operation, a full table scan must be performed, but it does not need to be as system-intensive as the **select *** operation Resources, but you know, after some calls from the relatively top-level sub_select function, all branches will eventually be called into the row_search_mvcc function, which is used to read a row from the B -tree structure stored by the InnoDB storage engine to memory In one of the buf (uchar * ), it will be used for subsequent processing. Locks, MVCC, etc. may be involved here, but row locks are not involved. This is for reading a row of data in mysql, but it does not need to read a whole row of valid data. At the code level, it will be in evaluate_join_record The lines read are evaluated in the function. So, in my opinion, whether you use order by or not has little effect on count(*).
CodePudding user response：
Responding to your specific question, it is not ignored nor optimized by the DB engine. Performance is affected by # of rows not the # of keys.
Maybe you can find further info here