In general, the optimizer will skip the sort procedure for the
ORDER BY
clause if it sees that the rows will
be in order anyway. But let's examine some exceptional
situations.
For the query:
SELECT column1 FROM Table1 ORDER BY 'x';
the optimizer will throw out the ORDER BY
clause. This is another example of dead code elimination.
For the query:
SELECT column1 FROM Table1 ORDER BY column1;
the optimizer will use an index on column1
,
if it exists.
For the query:
SELECT column1 FROM Table1 ORDER BY column1+1;
the optimizer will use an index on column1
,
if it exists. But don't let that fool you! The index is only for
finding the values. (It's cheaper to do a sequential scan of the
index than a sequential scan of the table, that's why
index
is a better join type than
ALL
— see
Section 7.2.2.4, “The index Join Type”.) There will still
be a full sort of the results.
For the query:
SELECT * FROM Table1
WHERE column1 > 'x' AND column2 > 'x'
ORDER BY column2;
if both column1
and
column2
are indexed, the optimizer will
choose an index on ... column1
. The fact that
ordering takes place by column2
values does
not affect the choice of driver in this case.
See:
/sql/sql_select.cc
,
test_if_order_by_key()
, and
/sql/sql_select.cc
,
test_if_skip_sort_order()
.
ORDER BY Optimization, provides a description of the internal sort procedure which we will not repeat here, but urge you to read, because it describes how the buffering and the quicksort mechanisms operate.
See:
/sql/sql_select.cc
,
create_sort_index()
.