Optimizing SELECT Statements 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 | +----+----------+--------+ mysql>  SELECT * FROM ratings ORDER BY category, id; +----+----------+--------+ | id | category | rating | +----+----------+--------+ | 1 | 1 | 4.5 | | 5 | 1 | 3.2 | | 3 | 2 | 3.7 | | 4 | 2 | 3.5 | | 6 | 2 | 3.5 | | 2 | 3 | 5.0 | | 7 | 3 | 2.7 | +----+----------+--------+ mysql>  SELECT * FROM ratings ORDER BY category, id LIMIT 5; +----+----------+--------+ | id | category | rating | +----+----------+--------+ | 1 | 1 | 4.5 | | 5 | 1 | 3.2 | | 3 | 2 | 3.7 | | 4 | 2 | 3.5 | | 6 | 2 | 3.5 | +----+----------+--------+ # 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] 1688 Verify that the  prefer_ordering_index  flag is enabled: Example : First we create and populate a table  t  as shown here: 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 . 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: In each case, the rows are sorted by the  ORDER BY  column, which is all that is required by the SQL standard.