MySQL large index integers for few rows performance

A developer of mine was making an application and came up with the following schema

purchase_order int(25) sales_number int(12) fulfillment_number int(12)

purchase_order is the index in this table. (There are other fields but not relevant to this issue). purchase_order is a concatenation of sales_number + fulfillment.

Instead i proposed an auto_incrementing field of id.

Current format could be essentially 12-15 characters long and ra开发者_运维知识库ndomly generated (Though always unique as sales_number + fulfillment_number would always be unique).

My question here is: if I have 3 rows each with a random btu unique ID i.e. 983903004, 238839309, 288430274 vs three rows with the ID 1,2,3 is there a performance hit?

As an aside my other argument (for those interested) to this was the schema makes little sense on the grounds of data redundancy (can easily do a SELECT CONCATENAE(sales_number,fulfillment_number)... rather than storing two columns together in a third)

---

The problem as I see is not with bigint vs int ( autoicrement column can be bigint as well, there is nothing wrong with it) but random value for primary key. If you use INNODB engine, primary key is at the same time a clustered key which defines physical order of data. Inserting random value can potentially cause more page splits, and, as a result a greater fragmentation, which in turn causes not only insert/update query to slow down, but also selects.
Your argument about concatenating makes sense, but executing CONCATE also has its cost(unfortunately, mysql doesn't support calculated persistent columns, so in some cases it's ok to store result of concatenation in a separate column; )

---

AFAIK integers are stored and compared as integers so the comparisons should take the same length of time.

Concatenating two ints (32bit) into one bigint (64bit) may have a performance hit that is hardware dependent.

---

1. having incremental id's will put records that were created around the same time near each other on the hdd. this might make some queries faster. if this is the primary key on innodb or for the index that these id's are used.
2. incremental records can sometimes be inserted a little bit quicker. test to see.
3. you'll need to make sure that the random id is unique. so you'll need an extra lookup.
4. i don't know if these points are material for you application.