CODEWITHSUNDEEP
mysql
CogitoErgoSum
CogitoErgoSum

Reputation: 2907

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 randomly 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)

Upvotes: 0

Views: 84

Answers (3)

a1ex07
a1ex07

Reputation: 37364

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; )

Upvotes: 1

Gidon Wise
Gidon Wise

Reputation: 1916

  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.

Upvotes: 0

Jaydee
Jaydee

Reputation: 4158

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.

Upvotes: 0

Related Questions