content_image/780320.jpg
mysql> SELECT * FROM ratings ORDER BY category LIMIT 5; +----+----------+--------+ | id | category | rating | +----+----------+--------+ | 1 | 1 | 4.5 | | 5 | 1 | 3.2 | | 4 | 2 | 3.5 | | 3 | 2 | 3.7 | | 6 | 2 | 3.5 | +----+----------+--------+ 

In each case, the rows are sorted by the ORDER BY  column, which is all that is required by the SQL standard. 

If it is important to ensure the same row order with and without LIMIT , include additional columns in the ORDER BY  clause to make the order deterministic. For example, if id  values are unique, you can make rows for a given category  value appear in id  order by sorting like this: 

<img src='content_image/780320.jpg'>

For a query with an ORDER BY  or GROUP BY  and a LIMIT  clause, the optimizer tries to choose an ordered index by default when it appears doing so would speed up query execution. Prior to MySQL 8.0.21, there was no way to override this behavior, even in cases where using some other optimization might be faster. Beginning with MySQL 8.0.21, it is possible to turn off this optimization by setting the optimizer_switch system variable’s prefer_ordering_index  flag to off . 

Example : First we create and populate a table t  as shown here: 

# Create and populate a table t: mysql> CREATE TABLE t ( -> id1 BIGINT NOT NULL, -> id2 BIGINT NOT NULL, -> c1 VARCHAR(50) NOT NULL, -> c2 VARCHAR(50) NOT NULL, -> PRIMARY KEY (id1), -> INDEX i (id2, c1) -> ); # [Insert some rows into table t - not shown] 

Verify that the prefer_ordering_index  flag is enabled: 

