‫In this video, we'll look through how the database optimizer decides which index to use, especially
‫if there are multiple indexes.
‫This is very useful if you want to know whether your table needs an extra index or not, because if
‫you have multiple indexes, how do you know which index that database is going to use?
‫Is not going to use all of them all the time.
‫It really depends.
‫This video again explained this kind of footprint kind of cases where this video is not going to discuss
‫the combined index is one index that have multiple columns.
‫Right, because I already discussed that.
‫So let's say we have a beautiful table with just two columns.
‫If one if two column one has an index, if one index and column two has an index, F2 index, and we're
‫going to do a select star from the table where if one equals one and if two equal four.
‫So let's go through some scenarios to see how the database optimizer decide to play.
‫Should I use if one index or if two index?
‫There are so many ways to solve this to look for these values.
‫Right.
‫So let's take a little case number one, because number one is when the database optimizer decides to
‫use both of them.
‫So what it does is it queries if one idea looking for the value one in this case, finding all the row
‫IDs that matches and tuples in case of Postgres Reuters, in case of Oracle, and it goes over and it
‫collects all these are all ideas.
‫And then it does the same exact search, but with a value for on if IDEX so searches and collects a
‫different set of priorities and in case of an end it will essentially do an intersection like, OK,
‫what is the values that are in here, not in here, and then essentially merges the results and or is
‫going to result in essentially unioned them.
‫So that's that's how it does the effect of ResultSet.
‫The row ideas are are collected.
‫That's the final result.
‫You go to the table if necessary, obviously, and you collect the actual values from the table, from
‫the heap.
‫Right.
‫So, one, does it actually do this?
‫It really depends if the values are not too small of the if we know that we're not going to get so little
‫of a result, because if we're going to get a little result, it's not worth searching both indexes.
‫One is enough, right.
‫Especially in case of an end in cases gets really tedious.
‫And if we get too large of a result, we know that the end is going to turn so many rows, so many row
‫ideas.
‫It's just not worth looking through the index in this case.
‫We're going to go through the table index.
‫Right.
‫So it really, really depends on.
‫And that's all heuristics the database uses to decide what what of this.
‫Right.
‫So that's case one.
‫So let's talk about case to the case, too, is when the optimizer decides to use one or index over
‫the other.
‫So let's take an example.
‫We're going to show an example right here.
‫The database decides to use if one index only search all the rows for the value of one and then we collect
‫all the rows and then don't use the second index.
‫And let's just go immediately to the table, which those rows and then do a recondition really filter
‫the results based on F two equal four.
‫Right.
‫So databases do usually do that when they know that if two index returns so many rolls while F one returns
‫very few rows and the condition is an end, because if I know it's an end, then I know, of course
‫I'm going to go with a smaller one because anything that doesn't match if one right is out of the ResultSet.
‫Right.
‫So it's going to go off of one example is a primary.
‫If I have a primary, the database almost always going to use the Primerica index in case of an and
‫again or then or it's a different situation because you get more results.
‫Right.
‫And then go back to the table directly and fetch the results we want to write.
‫It's just not worth searching to indexes because trees are not cheap guys and even cemeteries are not
‫cheap.
‫Indexes are not cheap to search.
‫Yeah.
‫If you're searching, if you know that you're going to get so, so few of a values, then it's worth
‫searching.
‫Then if you know that you're going to get a lot of rows back, then it's not worth searching.
‫The index and maybe table scan is actually better.
‫And you might say, how do you know how does it know that, hey, this index, again, results so many
‫rows versus this search is going to result into fewer?
‫Well, there's something called the statistics.
‫The stats real, really powerful.
‫The database keeps stats on the table, says, okay, here's the table.
‫Here's approximately how many rows there.
‫Here's approximately how many ones are there.
‫Here's some of how many.
‫Wreaths are there and all this kind of value, so they are not 100 percent correct, but they give really
‫a lot of values and you can also always update these statistics with a common goal and allies or sometimes
‫I think gather statistics and oracle.
‫Right.
‫So that's the idea really.
‫Again, depends on an and or here.
‫But here's one example where we use one index over the other, just one and then filter.
‫So an index with a filter, that's case two.
‫And case number three is just the database decides, you know what, you guys suck.
‫Both indexes suck.
‫I'm not going to allow the search to get for the value of one equal one.
‫And if two equals four is just going to return to the whole table, almost the whole table, three quarters
‫of the table.
‫I did my math and this is what I think.
‫So guess what?
‫I'm not going to use both of you.
‫I'm going to already go to the index because I need to go there anyway, because I'm going to select
‫other columns that Airbus decides to search the social list.
‫So many rules, just like let's go through the table directly into a filter which is much cheaper.
‫And some databases like Bullsbrook does a threatening multithreaded and issue so many workers so he
‫can do multiple workers stood to scan the table.
‫Right.
‫So table statistics are very, very critical here.
‫So if you got some wrong result, right, let's say here's where things get nasty sometimes.
‫I got beat so many times on this.
‫Here's an example.
‫You bring a table, very fresh table.
‫Right.
‫Empty, right.
‫So that is statistics, almost zero.
‫It knows that a table is empty.
‫You them to enroll you in two rows, three rows, and then the tables get updated.
‫Every asynchronously right now knows it has only three rows.
‫And then you do an operation to insert three million or 300 million rows just in bulk.
‫And you're just immediately after you've inserted you just do queries.
‫You know what will happen in this case?
‫The is if you don't do the statistics, it will you will execute some something like this and we'll
‫say, hey, what should I do?
‫Let's let's look at this.
‫Oh, this table has only three rows, so it's always easier to scan it fully.
‫And as this case is going to do a full table scan, pulling all the 300 million rows to look through
‫this, I've I've got beaten in so many times by this because I forgot to update this or immediately
‫I, I did my queries after this.
‫So always, if you're using Postgres, do analyze was the other command vacuum, fold, vacuum to clean
‫up all that garbage.
‫And then, and then after that just I do an analyze in Oracle use gathers that the schema statistics
‫and SQL Server I forgot was the correct execute planner.
‫I forgot what it was called for the SQL Server.
‫But yeah, you can do all this kind of stuff guys, essentially.
‫And for those who want to advance stuff, if you know what you're doing, if you really know what you're
‫doing, you can use database hinting.
‫You can in the query include some sort of comments like this one is the one you see on the screen to
‫kind of force that database to say, hey, you database, you're dumb, you don't know anything.
‫Please trust me.
‫I want you to always use the index of EF1 or use the index of F2 because I have more knowledge than
‫you do because of certain situation, because my application I know what I just did.
‫You are not caught up to what I just did as a as a database.
‫So trust me, your stats are not up to date to what I am about to do.
‫Plus maybe a human in this case are way smarter than machines.
‫And unless I picks up anytime soon, I don't know.
‫I don't think it will.
‫But anytime soon.
‫So it is going to take time.
‫Right, guys?
‫That's it for me today.
‫Very quick video talking about how these databases use the indexing.
‫Very, very critical.
‫Again, I miss I omitted so many details, for example.
‫So we'll give we'll give this will open up for questions and we'll have some discussions on on what
‫if there is an order?
‫There is a join.
‫It completely changed that dynamic.
‫Right.
‫Is the building a database is not something easy.
‫It's really, really difficult.
‫But these tools will help you while you're scanning query at least you know, hey, I know that if one
‫is not going to have a lot of value, is going to have a let's say is going to have a lot of values.
‫I say have one, two, three.
‫That's only the unique value is going to have an aura.
‫I don't.
‫Let's take a real example.
‫Let's say you're building a customer database, right, and you have the states right in the United
‫States, like California, Texas, whatever, all of them.
‫Right.
‫And let's say your database is almost.
‫Exclusively, all people from California, right, and you have an index on the state field, obviously,
‫if all of them are California, that's just that's just a useless index right there.
‫Because, like, what is going to give me is if I search where state is California, that's the entire
‫table.
‫Right.
‫So but let's say you have three customers on Texas and one from Florida searching that one, that Florida
‫is way faster than searching the 300 million, 300 million.
‫That's the entire United States.
‫But do you get an idea if you have one value or few values?
‫Indexes are beautiful for this rhyme, but if you have so much rolls that you're coming back, that's
‫where you play the game of multiple indexes.
‫You know what?
‫I know that this rule, this index is going to give me this much rows back.
‫I'm going to slash it down with another index.
‫So let's put that.
‫But you have to think of how sparse your table looks like, right.
‫And in these kind of cases.
‫All right.
‫I'm going to leave you there, guys.
‫I'm going to see in the next one, you guys say goodbye.