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,
__________________________________________________________________________
OpenStack Development Mailing List (not for usage questions)
Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
