Why InnoDB Returns Rows in Primary‑Key Order When Scanning Without Indexes

Table creation and data insertion

A table test (a int, b int, primary key(a), key(b)) is created and rows are inserted with various values for columns a and b.

create table test (a int,b int,primary key(a),key(b));
insert into test values(1,1);
insert into test values(5,1);
insert into test values(3,1);
insert into test values(4,2);
insert into test values(10,4);
insert into test values(7,4);
insert into test values(8,5);
insert into test values(11,5);
insert into test values(20,6);
insert into test values(21,6);
insert into test values(19,7);
insert into test values(16,7);

Programmatic leaf order

A custom program ( a.out) reads the .ibd file and prints the order of leaf nodes in the secondary index b together with the primary‑key values.

[root@ora12ctest test]# ./a.out test.ibd 4
Index_no is:42
find first one record!
B:1,A:1-->
B:1,A:3-->
B:1,A:5-->
B:2,A:4-->
B:4,A:7-->
B:4,A:10-->
B:5,A:8-->
B:5,A:11-->
B:6,A:20-->
B:6,A:21-->
B:7,A:16-->
B:7,A:19-->

Query using secondary index (USING INDEX)

The optimizer uses the secondary index b. The result set follows exactly the leaf‑node order printed by the program.

explain select * from test force index(b);
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | test  | NULL       | index | NULL          | b    | 5       | NULL |   12 | 100.00   | Using index           |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+

select * from test force index(b);
+----+------+
| a  | b    |
+----+------+
|  1 | 1    |
|  3 | 1    |
|  5 | 1    |
|  4 | 2    |
|  7 | 4    |
| 10 | 4    |
|  8 | 5    |
| 11 | 5    |
| 20 | 6    |
| 21 | 6    |
| 16 | 7    |
| 19 | 7    |
+----+------+

Query without any index (full table scan)

Forcing the primary (clustered) index makes MySQL perform a full table scan, returning rows in primary‑key order ( a).

explain select * from test force index(primary);
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | test  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   12 | 100.00   | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

select * from test force index(primary);
+----+------+
| a  | b    |
+----+------+
|  1 | 1    |
|  3 | 1    |
|  4 | 2    |
|  5 | 1    |
|  7 | 4    |
|  8 | 5    |
| 10 | 4    |
| 11 | 5    |
| 16 | 7    |
| 19 | 7    |
| 20 | 6    |
| 21 | 6    |
+----+------+

Adding a third column to enable covering scans

A column c is added and filled with a constant value so that the secondary index can satisfy the query without touching the clustered index.

alter table test add column c int;
update test set c=100;
commit;

Index‑only scan with WHERE clause

Using the secondary index with a WHERE b IN (4,5,7) returns rows in the secondary‑index order, while forcing the primary index returns rows in primary‑key order, confirming the earlier observations.

explain select * from test force index(b) where b in(4,5,7);
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | test  | NULL       | range | b             | b    | 5       | NULL |    6 | 100.00   | Using index condition |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+

select * from test force index(b) where b in(4,5,7);
+----+------+------+
| a  | b    | c    |
+----+------+------+
|  7 | 4    | 100 |
| 10 | 4    | 100 |
|  8 | 5    | 100 |
| 11 | 5    | 100 |
| 16 | 7    | 100 |
| 19 | 7    | 100 |
+----+------+------+

explain select * from test force index(primary) where b in(4,5,7);
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | test  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   12 |   30.00  | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

select * from test force index(primary) where b in(4,5,7);
+----+------+------+
| a  | b    | c    |
+----+------+------+
|  7 | 4    | 100 |
|  8 | 5    | 100 |
| 10 | 4    | 100 |
| 11 | 5    | 100 |
| 16 | 7    | 100 |
| 19 | 7    | 100 |
+----+------+------+

Performance discussion

Secondary‑index leaf pages store fewer columns than the clustered index, so they occupy fewer pages. Consequently, index‑only scans typically have lower I/O cost and better performance than scanning the clustered index.

Conclusion

If different indexes produce different row orders, this is normal; InnoDB full‑table scans guarantee rows are returned in primary‑key order, unlike Oracle where an explicit ORDER BY is required.

In InnoDB a query such as WHERE b=… can use an index‑only scan, which Oracle cannot.

Overall, using an appropriate index—especially a covering secondary index—generally yields better performance than scanning the clustered index.