Database performance for application developers

Speaker 1

Get him in here.

Aaron Francis

Hello.

Speaker 1

Hello. I see him and I hear him.

Aaron Francis

Wonderful. Let me share my screen.

Speaker 1

I see it.

Aaron Francis

O-okay. And you're seeing the main one and not the presenter one, right?

Speaker 1

Yep, you're good.

Aaron Francis

All right, here we go. I'm gonna start my timer. Uh, we have a lot to cover in the next 40 minutes, so without any ado, Database Performance for Application Developers. My name is Aaron Francis. I'm a Laravel developer. I am dad to 16-month-old twins. And as of next week, I will be a developer educator at PlanetScale, which is a database company.

Um, I'm also the co-founder of Hammerstone, where we make components for your Laravel and Rails applications, including one called Sidecar, which I demoed at the last Laracon. Laracon is sponsored by a lot of great sponsors. This talk is individually sponsored, and so I wanna say thanks to my sponsor, my wife. Uh, my wife is a full-time stay-at-home mom to our twins, and I wouldn't have the, uh, time or mental bandwidth to do this kind of extracurricular stuff without her.

So thank you for sponsoring, Jennifer. Why should developers care about database performance? Two reasons, I think. We are closest to the data, and we are closest to the access patterns. So, we are the ones that are writing the queries, the access patterns, to pull the data out and to put the data in.

We are the first line of defense because we are so intimately familiar with the data and the queries. In fact, we may be the only line of defense. If you don't have a DBA on your team or a performance specialist on your team, you might be the only line of defense. And so it's really important that as application developers, we have at least an understanding of database performance.

We're gonna cover all kinds of stuff today, um, primarily focused on making it fast. And it's weird to say that making it fast is optional, but if your customers are happy and you're happy and you sleep at night and everything works, your app is fast enough. Um, there will may become a time where the database becomes a bottleneck, but I don't want you to leave here and think, "Well, I have to change everything about my database and my application because some guy on the internet told me to." That's not true.

Everything is relative, so if it's fast enough, it's fast enough. But if it's not, maybe this will help. But in the beginning, everything is fast. You're just cruising. You're loving life. Every day is a new day. The only question is, "Well, what color Lambo am I gonna take to work?" And everything is great until it's not, and you're still equally as cool, but your database has become the bottleneck, and so now we have to figure out how can we fix that.

Okay. We're gonna cover three broad sections: schema, indexes, and queries. The schema is your first opportunity to make great decisions or, um, to potentially shoot yourself in the foot. So when you're designing your tables, you're setting yourself up for success when it comes time to design indexes and when it comes time to query things.

So, as you're designing your tables, I want you to keep three things in mind. One is you should keep your schema as small as you can get away with, but make it as big as you need. I don't want you to mangle-- I don't want you to potentially mangle your data to try to fit it into a slightly smaller column type, but I also don't want you to be generous.

Don't, don't give your data, um, room to grow that it doesn't need. It should be as small as you can get away with, but as big as you need. A good example here, I think, is integers. I think most developers just call, when they're writing their migrations, they just call table integer and move on. And if it's, if it's signed, that gives you a, a max value of two billion.

And if it's unsigned, it's four billion, and that's, that's a lot. Um, if you know that your data ranges from zero to 100, and that's it, ever, you should make it an unsigned tiny int. And there's a lot of like, um, there's a lot of judgment call and a lot of art and a lot of, um, experience required to design good indexes and queries.

Designing a good schema is, is kinda just science. There's no real judgment calls about it. Look at your data, figure out what the smallest column type that will hold the biggest range of your data is, and use that one. And I know you may be thinking like, "Man, this does not matter. We're talking about one byte versus four bytes." Well, this is a database performance talk, so it kinda does matter.

Um, but also you want to apply, like you want to apply this thought process across your entire schema. So we're talking, you know, dozens of columns across dozens of tables across billions of rows. And at that point, it, it does kinda matter. You want your data to be as compact as you can have it on disk. Not necessarily because disk is expensive, but because you wanna minimize the reads to the disk.

And then if you do index it, you wanna keep it as compact as you can in memory as well. You wanna keep your column types simple. Prefer integers to strings. If the data's numeric and it's an integer, use integer. If, if it's a date, use a date. Don't use a string. You wanna use the simplest column type that you can.

Um, but you, you don't wanna, again, you don't wanna mangle your data. You don't wanna, you don't wanna try to squeeze stuff into the wrong column type. Strings exist, and they're totally fine. If your string is truly fixed length, so it's like a two-character, uh, a two-character abbreviation, use a fixed length column. Um, if you have to use a variable length column, make it as small as you can.

And if you have to use a blob, that's fine, but if you can get away without it, that's great. So where, where's an example in, I think, Laravel that we can potentially use a slightly simpler schema versus a more complex one? So if we have polymorphic relationships in Laravel, which we do, um- The-- it gives us this nice morphs method.

Under the hood, this morphs method expands into this. So we have an images table, and the image could be attached to multiple different models. So if we look at this, this string is g-gonna be something like app, models, user, we'll say. Uh, the imageable ID is going to be the primary key of the user.

So in the database, you'll see app model users and then one, two, three, four. And then it gives you an index. So this is what that shorthand expands to. I don't like to see fully qualified class names in my database, and so I use this really nice method that Laravel gives us called enforce morph map. Um, and now instead of using the fully qualified class name, it's gonna use whatever string I give it.

And I like this better because it, it decouples your data from your application a little bit, so it makes it less brittle. So now you can refactor, rename these classes and not be in a world of hurt, right? So if we enforce this morph map, now instead of the fully qualified class names, it's gonna use user and post. Well, we don't need two hundred and fifty-five characters here.

We can get away with just four. Why not? It's shorter, it's better, it's faster. Now, does-- is this gonna change your life? No. Is it good practice? Absolutely. If you want to go crazy, well, the, the one potential problem with this is, um, you would have to mangle your data just a little bit because you have-- you're now restricted to four characters. I don't mind this.

This doesn't bother me. I'm actually not in the habit of looking in the database at these polymorphic associations, and so seeing vid instead of video wouldn't bother me. If you want to mangle your data a little bit, you could turn these into integers. This is actually what I do. It's a little data mangly, but again, I'm not in the habit of, I'm not in the habit of inspecting the polymorphic table very much.

So now instead of any string whatsoever, you'll have integers, and now you have room for two hundred and fifty-five different polymorphic relationships, which seems unlikely to me. Um, so should you do this? I don't know. It's kinda, it's kinda up to you. I like the intermediate of using a fixed-length character. You could use an enum, which is, like, gives you the readability of a string with the underlying storage of an integer.

I don't love those `cause you have to do an altered table to add new ones, and that annoys me. Um, so this is just kind of a good example of, like, if you spend a little bit of time, um, inspecting your data, you may be able to design a slightly tighter schema. Finally, your schema should be honest. It should represent reality.

If your data is not nullable, don't make your column nullable. But if your data is nullable, don't lie about it and then put some magic string to represent null. You should avoid null, but don't make up your own implementation of null. The database can handle null, but your, your schema should match reality as much as you can and make it as tight and compact as you can.

Schema is cool. Indexes are way cooler. Um, indexes are like ten thousand times more powerful than schema at, um, increasing the performance of your database. Having a good schema sets you up for better indexes, so we kinda had to talk about schema. When I am talking about indexes here, I am primarily, or exclusively maybe, talking about B-tree or B-tree plus indexes.

There are other types. There's full-text, spatial, maybe R-Tree, GiST indexes. I know little to nothing about those, which is fine because B-tree is the most common. So, an index is a separate data structure from your table. Your table ha-- Your table's actually an index, and it has all the data in the leaf nodes.

A, um, an index is an, a separate data structure altogether that maintains a copy of part of your data. So when you create an index, it creates this separate data structure and takes some of the data over there. It also has a pointer back to the main table, back to the row.

And in MySQL, with the InnoDB engine, that pointer is the primary key. So if we have a table full of extremely famous people born in 1989, and we add an index on the first name, this data structure will be created. You can see that it's out of order from the table. The index is ordered by first name, which is what makes it work.

And then the primary key is appended to the index. So there's a lot of discussion about should I use UUIDs, should I use GUIDs, should I use ULIDs, should I use integers? This should tell you if you add an index to the last name, you get the primary key again. This should tell you that using GUIDs or UUIDs is not free because that data is going to be copied everywhere.

And so sometimes you have to use UUIDs because maybe of your, uh, the way that your replication is set up or something. But if you can get away with it, primary keys as integers is great because they're super small, and it's gonna be copied everywhere. I told you, you could look at your data, and you use your powerful brain and come up with a really good schema.

Your schema is driven by your data. Your indexes cannot be driven by your data whatsoever. Your indexes are driven by your queries. You have to look at your access patterns to design, um, effective indexes. A couple of rules about designing indexes.

You should have as many as you need. Indexes are the most powerful thing to give you good performance. You should have as few as you can get away with. Do you see a theme here? Um, that, that separate data structure is not free to maintain, so you should have as many indexes as you need. Don't be afraid to add them. What you should be afraid of is adding indexes that are worthless because they're not free to maintain.

Every time you add a row or update a row in the main table That separate data structure has to be modified, and you may add a row at the end of the table, which adds a row to the middle of the index. So stuff has to be shuffled around, and pages have to be split, and so it's not free. Don't, don't let that scare you away. I'm hoping that that scares you away from adding an index on every single column.

Um, the final thing I want you to consider is the entire query. I think it's really common to look at a query and look at the where statement and say, "Okay, I need, I need an index to cover that." But you also need to consider ordering, grouping, and joining. Don't just look at the where, look at the entire query, and that'll help us d-design good indexes.

This is the table that we have been using, um, and we're gonna use for the next couple of examples. It's very simple. You can see, however, that I've limited first and last name to 50 characters. Um, is that enough? Probably. I know that 255 is too much, so I'm already trying to keep that schema as compact as I can. We're gonna add this very vanilla lame index to the table on birthday, and let's look at some of the queries that this, uh, this index will help us, um, i-in

a sense, the performance. So if we want, if we want direct access to people born on February 14th, 1989, that query is-- that index is going to help this query for sure. You can also do a range of all people born in 1989. This bounded range, it will scan through the index. You can do an unbounded range and find the people born before 1989.

Because the index is ordered, it really does help a lot, in most cases, with ordering. Because the index is already ordered, it doesn't have to grab the rows and then reorder them by birthday. It can use that index to assist the ordering. What's nice is you can use that index kind of twice. It's read once, but you can use it kind of twice, once for filtering and once for ordering, and so this query is gonna be awesome.

Finally, you can use it for grouping. So if you want to count up the people born on a certain day, this index is gonna help you. This is good information to have. This does not thrill me. I'm not moved, I'm not moved by this information. This isn't that exciting to me. What's more exciting is when we start talking about multiple column indexes or composite indexes.

Instead of creating separate indexes on first name, last name, and birthday, we're gonna create a single index that covers first name, last name, and birthday. If you find that your tables are full of single-column indexes only, that is a good hint, not absolute proof, but that is a good hint that there's a lot left on the table that you could potentially optimize.

Some rules. Uh, databases are just rules, apparently. Some rules for, um, composite indexes that you need to keep in mind. Super-duper important, left to right, no skipping. The database will work through this index from the left side to the right side, and it cannot skip a column. What I mean to say is first name, last name, birthday is left to right.

So it's really important the order that you define your composite keys in, 'cause that's gonna dictate how the index can be used. And we'll-- I'll show you some examples and some proof of this, but just remember, left to right, no skipping, and it stops at the first range. Okay? So left to right, no skipping, and stops at the first range.

I promise you're gonna remember these. We'll cover them a bunch. Taking this new composite query, which of these old queries that we looked at will be aided by this index? The answer is none of them. None, none of these queries will use this new composite index because we didn't go left to right. We skipped first name and last name. These queries are full of references to birthday, to say nothing of first name and last name, and so the index doesn't work here.

Which queries might work here? Um, if you have equality, if you have three equals across the three columns, that's gonna be great. You can also just use the first two columns. You don't have to use the whole index, but you can. In this case, we're gonna do first name Aaron, last name Francis, which will be assisted by the index. Same goes for just first name.

So now you can see composite indexes can kind of cover a whole range of queries if you design them in the right order and you design them based on your queries. Working kind of back out, you can also do a range on the first part. On the left prefix of the index, you can do a range. You can do an equality statement, first name Aaron, and then a range on the second one, or you can do an equality statement on first name, last name, and then do a range on birthday.

So these are the ones that it will work with. Let's look at f-a few that it won't work with. Look at this guy. So we need to go left to right, which we're doing. Stops on the first range. So the index will stop being used after the first name column because that's a range. This query will still run, but it won't use last name for the index.

And again, left to right, no skip-sies. You have first name, and then you skip last name, and then you have birthday. So the index will only be used for the first name part, not for the birthday part. Let's get out of those slides and have a little more fun here. Okay, so this is not school, so let's do some fun stuff.

Select star from people. Um, we'll say limit ten. Okay, we have people. We have people in this database. That is good. Explain, select star from people where, uh, what is it? First name equals Aaron. Okay, here we go. Now we're having some fun. Look at this from MySQL.

It's telling us it's gonna use the first name, last name, birthday index. So I'm gonna shrink that because it's huge. That tells us what key is being used. This is the thing that I think we want to pay attention to right now. The key length reports two oh two. What does that mean? I'm not gonna tell you. Um, we're gonna figure it out together. So the key length reports two oh two, and we used just first name.

Now, if we add and last name equals Francis, the key length changed to four oh four, which is double two oh two. And because first name and last name were both declared the exact same length, it does seem like we're using two columns of the same length, first name and last name. Pretty cool.

If we add birthday equals 1989/02/14, the key length jumps to four oh seven. So that tells us that the more of the key we access, the bigger that number gets. Right? So that's the number of bytes that is able to be used from the key.

Now, with this information, we can, um, hopefully prove, maybe disprove, but we can hopefully prove what I was telling you earlier. Left to right, no skipping. So if we skip last name, I-- meaning we don't have a condition on it, only the first part of the key is used. The two oh two part is used. Importantly, it's not two oh five, which would be seemingly first name and birthday, because that is not allowed.

It's two oh two. So if we do-- we put it back, we go back to four oh seven, the entire key is being used. If we change this second condition to a range, say less than Francis, then look, we're using, we're using the first two parts of the key, an equality on first name and a range on last name, and then birthday is totally hosed.

We can't use that, right? So this proves, this proves what I was telling you about the access patterns and the range. Now, while we're here, let's talk a little bit about sorting. So probably the worst thing you can see, uh, probably the worst thing you can see is order by using filesort.

So if you see using filesort, that's usually bad news. Um, that means an index is not being used. It is not always avoidable, so don't freak out if it's there. If the result set is small enough, it can be totally fine. But this is an indication that an index is not being used. It doesn't always write it to a file.

It can do it in memory sometimes, but it's still, it's worse than using an index. Okay. For sorting, the same rules apply, but it's a little, it's a little bit different, maybe with some more nuance. Left to right, no skipping, stops at the first range. So if we were to order by first name, the filesort goes away.

That's great. That makes sense because first name is the left prefix. If we were to order by last name, we still get no filesort, and that's because the first part of the key has been unlocked by the where condition. So it, it is now available, but also the next one is now available. So we're either-- we're able to use either first name or the last name, or both names, because both parts of those, of that key is available for usage, right?

So if we did, um, and last name equals Francis, we're still not gonna get a filesort, and now we also have access to sort by birthday. Because first name and last name have been unlocked, and so now birthday is available to us to use, and we could do, uh, we could do first name and birthday, and we're not gonna get a filesort.

Now, if we change this to a range, stops after the first range, and we're hosed again. So it's important to remember left to right, left to right, no skip-sees, stops after the first range. After a key has been unlocked, after a key part has been unlocked, it's ab- it, it-- you're able to use it for sorting. So we switch this back to equals, order by birthday, we're good to go.

Let's talk about something else. I want to talk about something called covering indexes. Um, there is no command to create a covering index. A covering index is not a special type of index. It is a regular index in a special situation. So don't go try to figure out how to create a covering index.

Um, not possible. What a covering index is, it, it is the situation where an index can cover the needs of an entire query. I told you that, um, an index is a separate data structure, and it contains a, um, con-contains a copy of the primary key to get back to the table so that it can pull the rest of the data, right?

Sometimes, sometimes it doesn't have to go back to the table. Sometimes everything it needs is in that separate data structure, and in that case, you're in super good shape. Um, if we look in this extra column, which is where MySQL just puts a bunch of stuff and is like, "Good luck with that," um, it says using index. It would be more helpful if it said using index only, because that's what's really happening.

So if you see a query that says using index, that means the entire query has been satisfied from an index without going back to the main table. So you have first name, you have last name, you have birthday. All of those things are in the index. But remember, ID is also in the index, not because you put it there, but because NODB put it there.

So any combination of these four columns, you can select, and it'll be satisfied completely by the index. And this is like triple gold star best kind of index you can have. If you add something that's not needed, the using index, or that's not available, the using index goes away. I don't design for covering indexes very often.

The reason is we live in a, in, uh, an active record world, right? Eloquent issues a select star to populate your model, which I'll talk about later. And so this is the query that is most often run by our application So I don't optimize for covering indexes for that. If you are writing standalone queries and you're doing it kind of, uh, freehand, not like as a, as a eloquent populating a model, and you can find a covering index, that's incredible.

That's gonna be super fast. You're gonna be real happy about that. But for the web app itself, nah, it's kind of hard to get. Let's keep going. I love this no slides thing. If we have, um, if we have some JSON, how can we index it? Well, you can't do it directly, and that kind of sucks. So I've designed this horrible table, um, with a column for name and then a JSON blob, and I've hid the email in the JSON blob.

Terrible design. Put the email on the top level if you're gonna search by it, but it makes for a great example. So how do we index this JSON blob if we can't index a JSON blob? Okay, let's look at a way to do this in Laravel. We have our JSON data table. We're gonna add, um, a string for email.

We're gonna restrict it. Is 100 too much? Eh, who knows? Um, now, you may be thinking, "Okay, I can use a Laravel model observer. Every time the model changes, I can pluck the email and put it in this column." You super-duper can do that. That feels a little brittle to me, um, because every update then has to be issued through the model observer.

And sometimes you turn off events, you do mass updates, and sometimes you just update, um, you just go to TablePlus and update it yourself. So I don't love that. What we're going to do instead is we're gonna tell the database, "I want you to keep a computed column." So, um, you can have like a calculated column or a computed column, kind of like a computed property, um, a virtual column.

And what this does is this puts the responsibility onto the database to keep our new fake kind of made-up column, to keep it totally up to date at all times no matter what. You can look down, if we do a little source dive here. This virtual as accepts, um, an expression, which we'll have to figure out somehow, and it creates a virtual generated column, and the Laravel drivers that support it are MySQL, Postgres, and SQLite.

So this seems cool. Let's do this. Um, what are we gonna put here? How are we gonna get the email out of the JSON blob? I'm not gonna do that. I'm gonna make Laravel do that for me, and there are a couple reasons. One is I don't want to mess it up on live, live on video, and also, um, it's super-duper important that we copy exactly what Laravel does, and I'll show you why in a second.

So from this, uh, I think it's called JSON data table, we're going to select, what's it called? JSON arrow email. This is Laravel's nice way of like papering over some monstrosities. Um, so I'm gonna do this. I'm gonna dump it to, yep, yep, yep, artisan test.

Here we go. This is what Laravel turned that JSON arrow email into. Turned it into this. That's great. That looks reasonable to me. I'm into that. So we'll take that and replace that and reduce some spaces. Uh, yep, yep, yep. Now we're telling, in this case, MySQL, we're telling MySQL, "Hey, I'm gonna make a string called email.

You are gonna fill it with JSON unquote JSON extract JSON dollar dot email." And MySQL's like, "Yeah, I can do that. I'm a database." We can just add an index on it just like that. So now we have an index on this JSON key. Let's run this migration, AR migrate. Okay, good there.

TablePlus, refresh the table. Look at that. Pretty cool. Um, we have this generated column that we don't maintain, and if I change, uh, what is this? More Alexies. If I change that to some Fubar, it changes to some Fubar. MySQL did it for me. If I change it over here, uh, uh, uh, you don't own that column.

That's not your column. MySQL is responsible for that column. So you can't change it, which is great. That's what we're after. Let's take a look. Select star from, uh, JSON data where email equals Aaron at example. You thought I was gonna give you my email address? Come on. Um, okay. So there's the one row.

That's me. Let's see what is going on under the hood. Look at this. The key that is being used is that email index. That's pretty cool. So we're now, we're, we're now able to index indirectly JSON blob. The frustrating part is you can't use Laravel's arrow syntax anymore because there's this new like virtual generated column that has an index on it.

And so now you're like telling your developers, "Oh, please do not access the JSON blob. Access this column." And worse, there may be third-party packages that you don't control that are using the old way. Well, that's not entirely accurate. Um, one of the reasons, one of the reasons besides laziness that I had Laravel come up with this access pattern is because if you are super careful, if you're super careful, MySQL will look at this and say, "Hang

on a second. I have a generated column that has an index on it that matches this expression. Why don't I just use that? I'll just use that instead of like going through the JSON like an idiot and manually filtering it." So MySQL is smart in that regard. If you dork up the access pattern, MySQL's gonna look at it and say, "No, I don't recognize that at all." So what, what I always do is I want to see how is Laravel doing it.

I'm gonna copy that and use that style for my generated column. Now, every access pattern, whether email or using Laravel's, is going to work. Third-party tools that don't know anything about this generated column, totally still gonna hit the index. In MySQL 8, I think 8.023 or something, there's something, uh, called functional indexes that can do a little bit of this song and dance without the generated column in the middle.

I haven't used them, so I can't really speak to them. If you are using MySQL 8, um, you can also make this column invisible. So if you don't want to see this email column, um, when a, when you do a select star, this email column won't show up. You would have to explicitly select it. So is it useful? I don't know.

It's kind of cool. Um, I kind of like the invisible because that helps with some of the select star problems anyway. Okay. There have been dozens and dozens of books written on queries, or I'm sorry, written on indexes, um, and I just covered indexes in like 20 minutes. So that is not an exhaustive, that is not an exhaustive, um, look at indexes.

When it comes time to querying, there are a couple things you need to keep in mind. The first thing is please for the love of God, use your indexes. Ideally, your indexes are based on your queries anyway, so you don't really have a choice. Um, but any tip or trick I give you is basically gonna be, how can I use an index better? So use your indexes.

Do not obfuscate your indexes. Don't hide your indexes from MySQL. This query, very simple, will use an index on first name. This query, very simple, will not use an index on first name. You have wrapped the first name column in a function, and MySQL looks at that and says, "Nope. Nope, not me.

I don't have that." And it just scans the whole table. So don't obfuscate, don't hide your indexes in some kind of function. And I know what you're thinking, "Aaron, you fool. I would never do such a thing." Wouldn't you, though? So what if we look at, what if we look at, I don't know, people, and we say where created at, and you're like, "Well, I want to say where created at this year." So I'm gonna do where year created at, and let's just debug that and dump that.

Oh, no. What, what bad news. What a terrible outcome. You've wrapped what could be potentially an indexed column in a function, and so my, MySQL looks at it and goes, "You're on your own, man." Could you make a generated column for this? Sure. Don't make a generated column for everything. Um, there's a better way to do this one, and that's just to use a where between.

I know it's 2020, so I'm gonna do start of year, and I'm going to do end of year. And if we debug that, now this column is not obfuscated, and it's using a range. Is this Eloquent's fault? 100% no. Um, Eloquent is a sharp tool that you could cut yourself with. You need to understand what is happening under the hood.

Um, there are times where, like, using these handy Eloquent methods are the best you can do. To, to do a where month and not obfuscate the created at column, I, I actually don't know how you do that. You can't turn this into a range. And so using Eloquent is great. Use Eloquent as much as you can. Please don't write raw SQL when you can use Eloquent.

But you do need to know what's going on under the hood because you might accidentally shoot yourself in the foot where you thought you were being clever. Here we are again, select only what you need. The common wisdom is, um, don't use select star, otherwise you're a noob. So this may be a little bit of a hot take. I'm gonna say select only what you need maybe.

I don't... This may make me a bad database guy, um, but select only what you need maybe. When we're living in an active record world, which we are with Eloquent, and it's amazing and we love it, I don't like monkeying with the select star. Here's what could happen. You could optimize your select star that then populates your, um, Eloquent model, right?

So you're selecting a portion of the columns, and you're populating your model, and your model is partially populated. If you are not extremely careful, you could take that partially populated model and then start accessing things that weren't populated and running code paths as if those attributes are null when the attributes are just in the database and you didn't select them. So that makes me really, really nervous, so I don't like to partially populate Eloquent models.

If you are writing a query that doesn't populate an Eloquent model, select only what you need. Optimize how much data you're pulling. That's great. There's potentially a happy medium in an active record world, and that is if you have a table that is potentially very wide, 60 columns or something, and potentially very long, and there are columns that you use extremely frequently, and there are columns that you use hardly ever, you can shuffle those old crusty columns off into a separate

table, i.e. a separate model. So you may have a user table that has a bunch of, um, supplemental or meta or addendum information, and you're just carrying it around. It's just, it's just weighing you down. You could potentially separate that in, into a meta table and use Eloquent to, like, eager load it if you ever need it.

And then that way, your, um, your hot columns, the ones that you're accessing all the time on this big table that you're reading all the time can be, whoop, compressed into disk much, much better. Would I do this? If I had a compelling reason, yeah. Um, I would wait until I had a compelling reason. But this, I think, is a happy medium between, um, a DBA saying, "You should never use select star," and an app dev- developer saying, "You should always use select star." Eh, you can kind of split the difference.

Another method you can do is, um, get close quickly and get right slowly. Um, if you're in a situation where you're trying to select star from tasks where, uh-oh, we have another function that is potentially obfuscating a, an index on due date. Could you- Could you add a generated column?

Sure, but don't just add a million generated columns because they're cool. They are cool. If you have an index on due date already, you can add what's called, um, a redundant condition by saying due date greater than, um, today. This is called a redundant condition because this won't change the results at all. This is like saying, "Give me, um, give me numbers that are bigger than three and are also bigger than two." And you're like, "Yeah, yeah, that's redundant." But this redundant condition

down here is totally available to be used should there be an index on due date. And so the theory here is that you can quickly chop off a whole bunch of records using the index and then slowly filter down to exactly what you need in the smaller result set. These are fun to find. I don't find them that often, but if you can find one, they're really fun `cause they're easy to add.

You just change the application code a tiny bit, and you're off to the races. I do this a lot with geographic searches. If I have a point of interest in the middle and then a compu- complicated, like Haversine, whatever, to figure out the, the radius around it, I just make a, a big old ugly bounding box, and I just chop everything off, and then I use the expensive calculation afterwards to eliminate those false positives.

So the, the bounding box is a method of a redundant condition. Last one, let's talk about why subqueries are bad. Subqueries aren't bad. So sorry for the tease. Subqueries are great. Um, you might hear, um, you might hear people say, "Every subquery needs to be rewritten as a join." And I mean, technically they're right.

The database itself is gonna rewrite your subquery as a join. So the database has the optimizer, and the optimizer is crazy smart and sometimes stupid. But the database will take your subquery and oftentimes rewrite it into what's called a semi-join or an anti-join, and those aren't left and right joins. So one of the problems that I run into when I'm trying to filter on a related model, let's say, is when you join it in, you get this explosion of columns that you then have to, like, filter back down, and you have to muck with

your select statement. You could also get a geometric explosion of rows depending on what kind of join you're doing and how you do it, right? I like subqueries because it, it turns into this, like, semi-- it's called a semi-join, um, under the hood, and MySQL will say, "I know what you're looking for. I'm gonna run it as if it's a join, but I'm not gonna do any of that adding the extra columns you didn't ask for." There is one thing you need to look out for.

If you are on MySQL 5.7, this query will eat your lunch and your dinner and your breakfast. This is called a dependent subquery. Under the hood, this where has turns into a where exists. The subquery references a, a column on the outside query, meaning it is a dependent subquery. This will totally hose you, and I'm talking, like, two orders of magnitude hose you.

If you're on, if you're on MySQL 5.7, make it a, an independent subquery by using a where IN with a closure. This turns into an independent subquery. MySQL 5.7 is like, "Yeah, cool, I got it." MySQL 8 can handle both of them on the top. They're called trivially correlated, and it's like, "I'll just uncorrelate these. It doesn't matter." So that's just my word of warning for you 5.7 users.

I have no idea if Postgres suffers from this issue. I'm, I'm-- I don't know Postgres. Um, so just keep that in mind if you're on 5.7. Go look at your where haves and see if you can change it to a where IN. You might be surprised. That's it. This was a, this was a whirlwind. Um, that was a ton of information in a super short time. Um, I am doing a full course that's going to be a lot, a lot lower key.

It'll be available at planetscale.com/laracon. It's totally free. I was gonna charge just out the nose for it, and then PlanetScale was like, "What if we made it free?" I was like, "Oh, that's a good idea." Um, so go to planetscale.com/laracon to sign up for that, and I'll let you know when it's out. And then I spend basically all of my time on Twitter, unless you're my new boss at PlanetScale, in which case I'm never on Twitter.

I spend all my time on Twitter. You can follow me at twitter.com/aarondfrancis. Thank you so much. With negative 30 seconds left, Ian, I yield back to you.

Speaker 1

Awesome. Great job. I love database indexes, one of my favorite topics.

Aaron Francis

It's a lot of fun.

Speaker 1

It is a lot of fun. All right. Thank you, sir.

Aaron Francis

See ya.

Speaker 1

All right. Let's see. I'm gonna share my screen for a minute. Let's go here. All right. I believe this is our, our final sponsor for the day, uh, About You. Um, they're actually hiring Laravel developers, lots and lots of Laravel developers and other developers, um, and just other technical positions, actually.

I think when I looked just before, they have, like, almost 100 open positions. So if you're, uh, looking for a new job, definitely check out their career page. Um, just a few we grabbed here. Lead backend developers, senior developers, full stack developers, lead full stack developers. Um, lots of jobs there. Also, LaraJobs users as well.

So thank you so much to About You. They're always huge supporters, again, have sponsored lots of Laracons. Um, really appreciate them. And then also on the swag page, uh, this is the first time they've done this, uh, they actually have a 10% off discount code, so actually go use their store. And they sell, you know, clothes, so if you're-- I think you have to be in the EU, but, uh, if you are, then go ahead and check that out and go on a little shopping spree.

Uh, thank you again, About You.