CODEWITHSUNDEEP
mysqlperformanceindexingprimary-key
Divanshu Aggarwal
Divanshu Aggarwal

Reputation: 446

Primary Key using timestamp+ guuid

I have large data , will it differ in indexing performace if I have epoc+guid vs guid+epoc as primary key since epoc are sorted.

Upvotes: 0

Views: 148

Answers (2)

Rick James
Rick James

Reputation: 142560

INSERT: guid+epoc will be somewhat slower. It will become much slower when the table is bigger than can fit in the buffer_pool.

SELECT: If you tend to access only "recent" data, then epoc+guid could be significantly faster. ("locality of reference")

If you use "Type 1" UUID and shuffle the bits, then you get both features (uniqueness and time-ordered) in a single field. (Smaller is better.) More: http://mysql.rjweb.org/doc.php/uuid Also, MySQL 8 has equivalent functions.

To discuss further, please provide the type of data, size of data, and type of inserts/selects/updates/deletes.

Upvotes: 1

Neville Kuyt
Neville Kuyt

Reputation: 29649

Strictly speaking, a primary key is a logic concept, rather than a physical concept, but it's usually implemented using an index (which is a physical concept). On InnoDB, that index will affect the order in which data is stored. This means that when writing to the table, it will have to re-order the data in the order of the primary key if the primary key value isn't neatly appended to the end.

Therefore, using epoc+guid should be much faster than guid+epoc, as long as the epoc reflects the time of writing the data to disk. If the epoc is some other value - business transaction date, date of birth, whatever - the difference is harder to predict.

As GUIDs are guaranteed unique, I'm not sure why you would ever want to have guid+epoc.

Upvotes: 1

Related Questions