MySQL Forums
Forum List  »  Optimizer & Parser

Is the query order guaranteed using Btree indexes and no ORDER BY
Posted by: Ted Johnson
Date: May 29, 2019 02:12PM

Have two tables using InnoDB storage engine with standard MySQL 8.x.

Table A has columns c1, c2, c3, c4 with a Btree index on c1, c2
Table B has columns c1, c2, c10, c11 with a Btree index on c1, c2,c10

From the literature the below query rows/results should be ordered by columns c1, c2, c10 where c10 may have 1 or more distinct values.

i.e. do not want to do an 'ORDER BY c1, c2, c10' with the below query since this causes an internal sort and query runs a while when millions of rows are being queried. Without the ORDER BY the below query returns rows immediately given the Btree index and columns in the indexes.

SELECT c1, c2, c3, c4, c10, c11 FROM A, B WHERE
A.c1 = B.c1 AND
A.c2 = B.c2

Can a MySQL expert confirm my research and that the ORDER BY is not needed?

Thanks in advance.

Options: ReplyQuote

Written By
Is the query order guaranteed using Btree indexes and no ORDER BY
May 29, 2019 02:12PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.