Open Stack

On 04/05/2017 11:02 AM, gordon chung wrote:
>
>
> On 05/04/17 09:00 AM, Monty Taylor wrote:
>>
>> Please do NOT use uuid as a primary key in MySQL:
>>
>> * UUID has 36 characters which makes it bulky.
>
> you can store it as a binary if space is a concern.

this is highly inconvenient from a datadump / MySQL commandline 
perspective.


>
>> * InnoDB stores data in the PRIMARY KEY order and all the secondary keys
>> also contain PRIMARY KEY. So having UUID as PRIMARY KEY makes the index
>> bigger which can not be fit into the memory
>> * Inserts are random and the data is scattered.
>
> can store a ordered uuid (uuid1) for performance but arguably not much
> diff from just autoincrement
>
>>
>> In cases where data has a large natural key (like a uuid) It is
>> considered a best practice to use an auto-increment integer as the
>> primary key and to put a second column in the table to store the uuid,
>> potentially with a unique index applied to it for consistency.
>>
>> That way the external identifier for things like gnocchi can still be
>> the UUID, but the internal id for the database can be an efficient
>> auto-increment primary key.
>
> very good points. i guess ultimately should probably just test to the
> scale you hope for

there's no advantage to the UUID being the physical primary key of the 
table.  If you don't care about the surrogate integer, just ignore it; 
it gets created for you.   The only argument I can see is that you 
really want to generate rows in Python that refer to the UUID of another 
row and you want that UUID to go straight into a foreign-key constrained 
column, in which case I'd urge you to instead use idiomatic SQLAlchemy 
ORM patterns for data manipulation (e.g. relationships).

The surrogate integer thing is the use case that all database engines 
are very well tested for and while it is not "pure" from Codd's point of 
view, it is definitely the most pragmatic approach from many different 
perspectives.


>
> cheers,
>