Offline synchronization (Performance UUID as a primary key)

Question

I'm working on a project , where some clients have internet connection issues.

When internet connection does not work , we store informations on database located in the client PC. When we get connection again we sychronise the local DB with the central one.

To avoid conflicts in record ids between the 2 databases we will use UUID [char(36)] instead of autoincrements.

Databases are Mysql with InnoDB engine.

My question is Will this have an impact on the performance for selects, joins etc? Should we use varbinary(16) instead of char(36) to improve performance ?

note : We already have an existing database with 4 Go data We are also open to other suggestion to resolve this offline/online issue.

Thanks

Answer 1

Since you didn't say which database engine is being used (MyISAM or InnoDB) then it's difficult to say what's the magnitude of the performance implication.

However, to cut the story short - yes, there will be performance implications for larger sets of data. The reason for that is that you require 36 bytes for the primary key index opposed to 4 (8 if bigint) bytes for integer.

I'll give you a hint how you can avoid conflicts:

First is to have different autoincrement offset on the databases. If you have 2 databases, you'd have autoincrements to be odd on one and even on another.

Second is to have compound primary key. If you define your primary key as PRIMARY KEY(id, server_id) then you won't get any clashes if you replicate the data into the central DB. You'll also know where it came from. The downside is that you need to supply the server_id to every query you do.