[FLOW3-general] UUID and performance on InnoDB

[Karsten Dambekalns]

Hi.

On 24.04.12 19:37, Alexander Berl wrote:
> regarding uuids and innoDB databases: was it ever considered that uuid
> primary keys are relatively bad for indexing performance (and hence
> insertion/update/read) on innodb and if so, what was the reason to
> decide on that format for the identifier nonetheless? (see e.g.
> http://kccoder.com/mysql/uuid-vs-int-insert-performance/ or
> http://www.mysqlperformanceblog.com/2007/03/13/to-uuid-or-not-to-uuid/)

Thanks for that article, until now I never saw facts about that topic.

To answer your question: what Christian wrote is right, the main concern
was usability and "uniqueness across everything".

The latter is important if you think about the way auto increment
columns work:

 1. you create an instance of something
 2. you persist
 3. the id is generated

Now when you have a framework where DB operations are bundled at the end
of the script run, imagine you want to create a link to a detail view
for a just created instance. What id do you use in that link? Right, you
do not even have one yet. Persisting everything to get that id is
overkill and feels quite hacky.

So we need an id that is created when the object is instanciated. We
cannot use integers here, because the risk of duplicates is almost a
certainty. So we ended up with UUIDs.


When it comes to performance, I do not yet see a real risk - the numbers
in the mentioned article are way beyond anything currently handled by
FLOW3. And MySQL is by no means the only persistent storage that can be
used - other RDBMS, CouchDB, … can fill that gap if needed.

And, since Benjamin Eberlei (of Doctrine 2 fame) pointed out the
potential slowness of varchar PK columns in the past, I have some ideas
how we could overcome that problem. But since performance is something
that can be added when needed (as we have done in the past already),
those ideas have not yet manifested in code.


I hope that answer your question :)

Regards,
Karsten

PS: Impressive footer… SCNR
-- 
Karsten Dambekalns
TYPO3 Core Developer, FLOW3 / Phoenix Team

TYPO3 .... inspiring people to share!
Get involved: typo3.org