Eloquent Where ENUM vs Int Foreign Key: Speed Benchmark

An experiment investigation I had in one demo project, with unexpected results.
Full premium tutorial: laraveldaily.com/post/eloquen...
- - - - -
Support the channel by checking out my products:
- My Laravel courses: laraveldaily.com/courses
- Filament examples: filamentexamples.com
- Livewire Kit Components: livewirekit.com
- - - - -
Other places to follow:
- My weekly Laravel newsletter: us11.campaign-archive.com/hom...
- My personal Twitter: / povilaskorop

[@trispretty]

This was fascinating, cheers! I'm a life time member, I'm going to dive into this. Great research!

[@jeremyvanderwegen1467]

Thanks, learned that we can also use multiple column indexes!

[@mohammadsalloum1]

Thanks, very useful.

[@youneschibouti4555]

great work, thank you

[@wgblondel]

Haha, funny to see you doing this experiment too. Few months ago I wanted to experiment using Filament with a huge amount of data, so I cloned the Filament demo and seeded millions of records. The first challenge was to rewrite the seeders and factories in a way that the seeding process doesn't take ages to complete ... 😁 Then I had to optimize some SQL queries, and I noticed the same problem with the deleted_at fields. Had to add some indexes to improve the overall performance.

[@e.nelson]

My other issue with filament is that bulk selecting data on a resource table takes ages to hydrate, even worse when you want you to load a form to perform data on these deleted records.

[@JohnRoux]

@@e.nelson Yeah, the bulk select will do a count*, so at 5m rows+, it's really not a great option. I end up swapping to only allowing the bulk select for this page. You also need to swap to simple pagination to avoid that count query though

[@hurleyd9828]

Awesome video

[@laubannenberg5446]

At first I thought this was going to be about PHP enums with casts, not sql enums. It might be interesting to dive deeper into the pros and cons of using only PHP enums (design flexibility?) or only sql enums (performance? ) or both. And yeah, it makes sense soft deletes should be a BIG consideration for your use of indexes.

[@LaravelDaily]

PHP enums would have been my next step with status_number field without foreign key, but since it didn't give any performance benefits, I didn't pursue it further.

[@JohnRoux]

Another option/improvement here is to make a virtual Stored column for `is_deleted`, a tinyint notnull (boolean) that is computed from (deleted_at IS NOT NULL) Then you index that column. You get around the issue with nullable columns, plus your index size is far smaller. It's very seldom you need to check if it was deleted before X date, and those times you can look at the `deleted_at`. But 99% of the time your query is going to be just caring about that boolean value

[@LaravelDaily]

In theory, I agree with you. But I've tested a similar scenario, and "where is_deleted = 0" and "where deleted_at is null" had identical performance, when both indexed. Didn't check the index size, though, maybe there is a slight benefit there, to save some disk space.

[@JohnRoux]

@@LaravelDaily yeah, I suspect the difference here will be the not null covering and index size If there's only 1 nullable Field in the index, as the last index, then it definitely won't make any performance impact, though that index might be a lot smaller

[@lazycode258]

Good day! Do you have any laravel tutorials that employ this vue3, type script, pinia, and axios?

[@LaravelDaily]

No, sorry

[@vlatkoviamkd]

Please make a video for Computed properties in Livewire and be more detailed, like the last video of Josh Cirre in "Which Livewire method should I use?".

[@LaravelDaily]

I think it's all pretty clear in the docs, my video wouldn't add something significant.

[@vlatkoviamkd]

@@LaravelDaily maybe When to use computed properties, difference in performance and cashing with computed properties. And when NOT to use them. But either way very much appreciate your work, you are the best.

[@LaravelDaily]

In my experience I didn't feel much difference, to be honest. I would need someone to code me a demo project that I would be able to debug and compare. Too much work for too niche topic.

[@DoonSafari]

Hi, Please make a video series on system design video for a Laravel project.

[@LaravelDaily]

"system design video"? What do you mean exactly, can you rephrase?

[@user-wz5ui7ce7p]

​@LaravelDaily I guess he talk about something like common architectural things such as architectural layers, business domain separation from framework etc. As Laravel developer, I also would like to know how we can build Laravel API with best architectural tips. I guess in "Laravel world" we choose our own philosophy with some features and solutions, but maybe we still have other approaches for architectural things?

[@ultrasys]

I didn’t have the patience to finish watching the video, but I would like to share that every time I heard “tiny int”, my ears screamed in pain. There’s something called padding, and it costs in terms of processing. Padding is bad for performance even if you think l it is just a matter of applying a simple calculation. The problem with it is that processors are much more efficient in loading and transferring data in their “native” block size (and this depends on what operation is being done), generally (today, at least) 64bits. Not “tiny” at all. Add to that, the weak MySQL code base, which, to me, looses only to MS SQL Server.

[@abdullajonsharipov6504]

If not enum, column type varchar and index then ?

[@LaravelDaily]

Have you watched the video?

[@Ali-hh9oi]

It's obviously slower because you did it wrong, you had to make a constant class of statuses (different file for each entity status, for eg TransactionStatus, ShopStatus, etc) instead of saving that on DB

What you mean?

[@keenj]

He used $table->enum not classes. What are you talking about?

[@Fosterushka]

bruh, sql query have nothing todo with php

[@LaravelOnline]

Umm…. No…

[@373323]

wow