Eloquent Where ENUM vs Int Foreign Key: Speed Benchmark

hello guys today I want to tell you a story of debugging of eloquent and MySQL performance with enum and foreign Keys integer I've been doing that for the last couple of weeks when I discovered that in one of the demo projects the where status new by enum is faster than where status ID by integer foreign key so I decided to dig deeper why and I've published my findings in a tutorial on laral dailyc the full tutorial is available for premium members but in this video I will show you the main things that I found and why it may or may not be faster with enum and foreign keys so I will read this article for you skipping some parts which will be only for premium members but you would still get the idea so I will Zoom that in and here's the story the scenario is from demo filament from the official filament demo where this Badge of new orders executes this SQL query under the hood and status is an enum field nothing really fancy here no index in the database and according to laral deog Bar it takes 150 milliseconds on my local server with 1 million orders seated and my initial idea for that was let's change it to status ID with foreign key integer which should be faster so I did exactly that I've created two new tables status and Tiny status models with migrations and database tables and this is the part that I will will skip actually for premium members so if you want to have the full migration seeds and also repository of the full code subscribe to the membership but meanwhile the actual result of that change was this so status was the old field and then I added two new Fields with integers with foreign keys and then I measured that one changing the condition from where status new in the filament to status ID 1 and surprisingly it was slower so instead of 150 milliseconds I had 300 on the second attempt it was 281 and then tiny status ID which is Tiny integer wasn't better it was the same around 300 milliseconds then I took Benchmark laral class to actually Loop through 10 iterations of the same eloquent statement and the results were pretty clear enum was for some reason faster than those two integers tin integer was a bit faster than big integer for reason but not by much the next idea aidea was of course about indexes are they being used slow select query usually is solved with indexes so by default LEL when you do foreign or foreign ID it adds the index to that foreign ID column so that was the situation in that table so customer ID and number was from filament by default and those two foreign keys with status ID and tying status ID were indeed indexed and also I tried to use explain sentence in MyQ SQL here and used a great tool called MySQL explain by to BS Petry which showed that for enum there's no index to be used and then for those two Fields the index was successfully used length of the index for big integer is eight bytes and then for tiny int is one bite but what it showed is that indexes were actually used so there was nothing wrong with that part despite that enum full scan table was still faster then I thought what is that nested Loop join for those foreign keys so I decided to do another experiment with status number field without foreign key I thought maybe foreign key is the reason so if joining another table for some checking then that may be the reason of the slowness so I added status number with DB statement of updating and the result was still the same still around 300 milliseconds added that to Benchmark Mark still same thing no foreign key is still within 260 milliseconds still slower than the enum so foreign key was not the reason now keep in mind I'm doing that Benchmark on my local MacBook Pro with M3 processor so imagine that 260 milliseconds what would that be on a live server which may be much cheaper and I actually decided to measure that on the cheapest remote digital ocean droplet and this is the part I will intentionally skip from this video video again something for premium members if you want to see what that number is on the live server and how much slower that is I will just say that the slowness is not in percentages but in how many times it is slower meanwhile let's get back to the investigation the idea that actually belongs to my colleague modestas was about delete it at so that model of the order uses soft deletes and I thought since it uses soft deletes for both enum and Tiny int I'm comparing Apples to Apples here and it's not really affecting the performance but just out of curiosity I removed soft deletes and now instead of 260 milliseconds for tiny status that was only 23 milliseconds and then I thought wait a minute let's dig deeper and try to debug that one and the Benchmark for all of those four queries showed completely different results so now the enum was slower and not just slower but miles away slower than those integer fields and this is something that I was actually expecting when the initial idea of that optimization was started I was expecting numbers to be faster than were by enum without any index so in other words if we remove deleted ad enum speed didn't change but integer speed skyrocketed but of course we cannot just remove deleted ad because it's part of our application we need to work within that constraint and the solution to that that we came up with again with my colleague estas is composite index index on the field including deleted ad so index on a few Fields so we try those index on those two index on those two and two more pairs and of course we returned use soft deletes and this was the result now the query with enum was 31 milliseconds instead of 150 so five times faster and The Benchmark showed something along the lines what I was expecting in the very IAL idea I finally kind of found the spot of actually comparing Apples to Apples with the same situation where numbers are marginally faster than enums but not by a huge margin and in this scenario interestingly tiny integer doesn't get much performance difference although it takes less space on dis and speaking of space too many indexes is another problem to be having so when launching the SQL query for index size this is the result so remember each of the indexes that you add to the database table adds to the size of the database structure in our case for million records the size of the indexes was 17 or 25 megabytes in addition to the original indexes on that table from filament so be careful when adding too many indexes because that's what may happen index size actually becomes bigger than the data size itself which may not be a problem and you may not feel that but just keep in mind that the size gets bigger the final part of that tutorial was about eloquent caching so another solution to this problem so instead of having count on each page in this case for menu item in the sidebar you can cash the results for an hour or so so I've showed how to do that and I'll also leave that part for premium members of laravel daily as well as as I mentioned the full repository for that experiment you can play around yourself including the SQL file that contains million records of orders to reproduce that scenario and this article is just one example of Premium tutorials on larel daily so in addition to courses sometimes we publish just longer article deeper Dives on something like 17 laral Bad practices Reverb demo RIS in laral 5,000 words so you get the access to all of those as laral daily premium member now what do you think about my experiment in general so enum versus integers and deleted ad I'm actually planning a separate video separate investigation specifically on deleted ad and index in that separately so expect something on that topic from me in upcoming weeks on this channel but meanwhile we can discuss the current experiment and investigation what do you think let's have a chat in the comments below that's it for this time and see you guys in other videos

Channel: Laravel Daily

Views: 4,768

Keywords:

Id: 6rrb9zAVTgM

Length: 8min 53sec (533 seconds)

Published: Thu Jul 04 2024