Thelonias Thelonias - 1 year ago 63
MySQL Question

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

I have a table with 3 columns:

(auto-incrementing PK),

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
column. They appear to first be ordered by
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 Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download