Thelonias Thelonias - 3 months ago 11
MySQL Question

Inserts are out of order even with auto-incrementing primary key

I have a table with 3 columns:

id
(auto-incrementing PK),
product_id
,
project_id
.

product_id
and
project_id
are foreign keys. There is also a unique key for those 2 columns.

I'm finding that when I do a simple "SELECT * FROM myTable", the results are not primarily being ordered by the
id
column. They appear to first be ordered by
id
but then also the other 2 columns. So, I insert (5, 10) into the table and then (5, 6), then select them, I get:

id | product_id | project_id

2 | 5 | 6

1 | 5 | 10

How does this happen?

Answer

If you don't explicitly request an order with ORDER BY, MySQL displays the results in the order it reads them from an index.

I would infer that MySQL is using your UNIQUE index to read these rows, so they're read from that index in order by product_id first, then by project_id.

By analogy, if you read names from the telephone book, you read them in order by last name and then by first name. That's the order they're stored, regardless of which phone number was assigned first.

The id column is the primary key, which is implicitly appended to every non-primary index.

Comments