Laravel UUID with Foreign Keys: Speed Benchmark of Two Approaches

There are two common ways to use UUIDs: as a primary key, and as a non-primary field. With foreign keys involved, what would work faster, and by how much?

- - - - -
Support the channel by checking out my products:

- - - - -
Other places to follow:

[LaravelDaily]

Update: made a quick benchmark comparing UUIDs and relatively new ULIDs in Laravel.

Performance is identical, haven't noticed any performance bump in using ULIDs in the same project as in the video.
So not shooting the follow-up video, nothing to "report".

[AbdelghafourElmarzougui-zpkh]

For me I use Primary Integer key for relations and I use UUID just for Model binding to prevent show IDs in Route URL :)

[intipontt]

If I recall correctly, The laravel schema simply transforms uuid() into varchar(36) for mysql. Even with a pk index, it should be slower to query than an integer (or big integer) type. PostgreSQL has a proper UUID type.

[davidlyons]

Thank you to test that for us I was to lazy to test it! In my case I use UUID or slugs for the URLs and then IDs for the internal queries. Now looking forward for the ULIDs that I didn't know about it, great commenters!

[ricko]

ULID (with Laravel support since >9.30) is considered better than UUID in certain situations because it has a lexicographically sortable representation, meaning it can be sorted as a string, whereas UUIDs cannot. Also ULIDs are shorter than UUIDs

[travholt]

There are also ULIDs, which are shorter than UUIDs and sortable because they start with timestamps. It would be interesting to see if this has an impact on efficiency.

[Niboros]

Thanks for the interesting topic. I like these kind of videos, and love that you go the extra mile showing the use of the `Benchmark` facade.

I understand you try to start the conversation between us developers at the end of the video and like to open the debate about whether we should or should not use the uuid's over regular id's. But in this case I would like to see this answered in a (new) video as well. Maybe there is a good example of an open repo project using uuid's. I'd love to go into the trade offs (as usual when talking about software engineering) and really see all the pro's and con's on when to use what.

[stefanrakic]

Great experiment :) One more dangers with using uuids and relations, we see in video second query where you have some list with ids (where alpha_role_user.alpha_user_id in (list of uuids)) and very easy you can broke limit for how DB query can be long with this approach

[javiershaka]

excellent video, personally when I want to do optimization I go directly to the execution plans, I think that at the moment I have not seen a library where, in addition to the queries, they give you the cost in I know that it is not to delve much into the database engine but it is very important to know what things affect our applications.

[pavelnemoi]

Thank you for the video. UUID v7, which is based on timestamp like ULID (which means it is also sortable), would have a similar performance as ULID but perhaps a bit worse because of the required higher storage size. Does Laravel support different versions of UUID?

[nadjimmalade]

When there are millions record the difference is huge.
We had to deal with that. Our table threads, related to groups, and having messages.
we were oblige to add auto-incrent column and change the index.
The fact is that UUID are char, not Number.
When you have a lot relationship, it take more time to build query, and more time for Mysql to run queries.

[snarcraft]

Thanks for this experiment.
I also suspected that it would hurt the performance.

[gdogmalone]

“Challenging” issue I have with UUIDs is I’m migrating an old system that uses them as a primary key, but I want to use an auto increment as a primary id and use the UUID for public facing parts like your beta demo.

The issue is, how do I go about creating this with existing data and maintaining the relationships previously based on those UUIDs? It’s causing me pain! 😂

[acirinelli]

I use a “oid” column that is incrementing and stands for “order” id, for sorting etc. then a uuid id column to identify the object.

[errorlade]

Tough call, I would probably sacrifice performance for better security by picking UUID as primary key.

Having a mixture is good for performance and security but just having integer based primary key, there will always be that 1 coder who will use the integer primary key in the route/blade for record management instead of uuid.

[MichaelRimbach]

An Snowflake ID comparison would be nice

[ndeblauw]

Very nice illustration that indicates that UUID indeed is slower, and should be used carefully. And I am curious to see the same experiment of your video repeated with proper indexes applied to the pivot table in both cases. To people jumping to conclusions: this experiment is statistically not sound yet. E.g. I see comments below like "drain the performance by half", but that's a conclusion that simply cannot be made based on one experiment with a very limited amount of users. I guess that the performance gap will be a function of the size of the dataset, but have no idea how steep the slope will be...

[marcinrobertkazmierczak]

Just use Snowflake UUID. UUIDs with numbers mixed with letters is really slow i huge set of data. Snowflake is perfect solution for unique id and performance... not only with selecting data but also with creating new records.

[forestlynx]

Laravel uses UUID 4 versions. If you use the UUID version where there is sorting, the indicators will be almost equal.

[FISS]

Interesting, but appart from performance, when you need to take a look at the data to troubleshoot it, having integers forming the relationships is a lot easier to read and to work with. Try to imagine yourself reading the primary key of a row and trying to spot it in a collection of related rows ...