Since the last couple of weeks, I have been working on MySQL more closely. MySQL is a brilliant piece of software. I remember reading about all the so

Pankaj Tanwar's Blog

submited by

Style Pass

2021-07-22 05:30:04

Since the last couple of weeks, I have been working on MySQL more closely. MySQL is a brilliant piece of software. I remember reading about all the sorting algorithms in college so I was curious to know which algorithm MySQL uses and how ORDER BY query works internally in such an efficient manner.

Firstly MySQL optimiser analyses the query and figures out if it can just take advantage of sorted indexes available. If yes, it naturally returns records in index order. (The exception is NDB engine, which needs to perform a merge sort once it gets data from all storage nodes)

The index may also be used even if ORDER BY doesn’t match the index exactly, as long as other columns in ORDER BY are constant.

Here, you might feel Indexes on userId and mobileNumber enables optimizer to use index BUT this query has “ SELECT * ”, which is selecting more columns than just userId and mobileNumber.

In this case, scanning through an entire index, to find columns which are not in the index, is more expensive than scanning the table and sorting the results. Here, the optimizer probably does not use the index.