CODEWITHSUNDEEP
phplaraveleloquentuuid
Adam Lambert
Adam Lambert

Reputation: 1431

Using a UUID column vs. and incrementing ID and a separate UUID column in relational databases (Laravel, Eloquent)

I often see two ways of doing this:

1: UUID as ID

Schema::create('orders', function (Blueprint $table) {
    $table->uuid('id')->unique();
...

2: UUID and Auto-incrementing ID

Schema::create('orders', function (Blueprint $table) {
    $table->bigIncrements('id');
    $table->uuid('uuid')->unique();
...

Are there any significant benefits in either method? One point I think could see being a benefit in option is with eager loading. For example:

select * from `order_addresses` where `order_addresses`.`id` in (1, 2, 3, 4, 5)

vs.

select * from `order_addresses` where `order_addresses`.`id` in ('683d3bc7-edd7-4f12-a7eb-831bfc5e90eb','20d3d3f5-2b0d-45e0-9f17-f581317b3f97','907af98b-e433-4e55-a641-3f134ea9039c','7713462c-b8aa-4d11-a576-7d4634595a35','4a27368e-5ebe-43e4-bfaf-8be303a84318','e5e618d9-fd25-4f98-bc70-03bc378c338d','5aa3dd71-a4fc-44ac-a810-2e414372d1ed','9c62bbdc-2555-4239-81fd-365ada304619','a7f22427-b7e7-41c0-bc38-f84306f0bae6','386d8318-3da5-4de1-95d0-f144b53ed76d')

However I am not 100%. Can anyone provide any further arguments and/or validate the above?

Upvotes: 0

Views: 2847

Answers (2)

Adam Lambert
Adam Lambert

Reputation: 1431

A string-based UUID does actually have a performance hit in the case of MySQL (and a few others).

https://www.percona.com/blog/2019/11/22/uuids-are-popular-but-bad-for-performance-lets-discuss/

A common approach within the Laravel community seems to be to use an auto-incrementing big integer for the primary key and then add an indexed UUID along side.

You could also check out of the binary UUID packages that are available which stores the UUID more efficiently within the database.

Upvotes: 1

N69S
N69S

Reputation: 17216

UUID are better for n-tier application where each application can create it's own identifier without risk of breaking the "unique" rule.

There is no need to have both and the speed difference between unsigned big integer and UUID is small.

UUID uses more space, that's about it.

Upvotes: 3

Related Questions