0

If I have a table with an index on a secondary column, does fetching data using the secondary column guarantee that the results are always in a consistent order?

For example, say I have a table T, with columns PKColumn, ColumnA, ColumnB, ColumnC....

and an index on ColumnA, say in ASC

Does the query, select * from T where ColumnA = '<some-value>' guarantee that the results are always returned in the same order (based on the sort direction of the index created on ColumnA?)

java_geek
  • 17,585
  • 30
  • 91
  • 113
  • 2
    Without an `ORDER BY` there is *no* guarantee as to the order in which rows are returned. – Nick Jun 26 '20 at 02:05

2 Answers2

0

No.

In the absence of an ORDER BY clause the engine is free to provide the result set in any order. Even more so, the ordering may not be consistent over time. You could get the rows in one order today, but it could be different tomorrow.

Now, considering you already have an index on column ColumnA, then you can modify the query by adding an ORDER BY clause to ensure the order you want, as in:

select * 
from T 
where ColumnA = '<some-value>'
ORDER BY ColumnA

This query will provide the ordered result set you want at a marginal extra cost. If the number of rows you are retrieving is low (less than 1000) the performance change should be barely noticeable.

For bigger result sets there are performance optimizations you can implement. You'll need to provide the exact query, to help you in this case.

The Impaler
  • 45,731
  • 9
  • 39
  • 76
  • Thanks. I do not have any specific query; just wanted to understand the behavior. Can you provide any link to the optimizations you are mentioning. – java_geek Jun 26 '20 at 05:52
  • @java_geek - From a different point of view: http://mysql.rjweb.org/doc.php/index_cookbook_mysql – Rick James Jun 26 '20 at 06:06
0

an index on ColumnA, say in ASC

Does the query, select * from T where ColumnA = constant guarantee that the results are always returned in the same order

Short answer: No.

Long answer:

Case 1: If there is only one row with that value, there is no "order" to "guarantee".

Case 2: There are multiple rows with that value. Well, the index does not specify what to do for dup ColumnA values. InnoDB implements secondary indexes by tacking on the PRIMARY KEY column(s). So...

Case 2a: If the Optimizer chooses to use that index, it will be in PK order.

Case 2b: If it chooses not to use that index, then the Optimizer probably did a table scan. So, again, it would be in PK order.

Case 2c: You have something else in the query (more in WHERE, GROUP BY, LIMIT, HAVING, etc) Now all bets are off.

Case 2d: (Sometime in the future.) What if the Optimizer could perform this query in multiple threads? Now the results are jumbled together.

Bottom Line: If you want an order, use ORDER BY. Period. Full Stop.

And, don't worry too much about performance. If the Optimizer can fold the ORDER BY into something else (usually GROUP BY or the tacked on PK), then it will. This leads to the ORDER BY costing nothing extra.

Rick James
  • 135,179
  • 13
  • 127
  • 222