Question on querying like select max(B) from t where A=123

I wonder how the MySQL will deal with the statement? If both Column A, B are indexed.

I suppose there will be two ways to do.

1. a. Select all records from t that A==123 as a temp result b. find the max B one from the temp result and开发者_开发技巧 return. The time complexity might be O(lgN + m).

2. Get the record in one step, in other word, T(N) = O(lgN)?

Thanks in advance.

My instinct would tell me that unless B is nullable and B is sparsely populated (really sparse, as low as 1% or lower as well as numbering less than 10% of the average number of values per index key A), such that inspecting B in descending order then checking for A=123 on those records is worthwhile, MySql won't have a bar of the index on B for this query.

More than likely it will just use A (if A is selective enough), retrieve from the table the records, sort by B descending and return the result.

This would mean your 1st case, O(N + m). N is directly proportional to table size, which is also statistically how many records on average would satisfy A={any x}