‫Welcome to another Q&A lecture.
‫This is for the month of May 2022.
‫And the question for today's lecture is from Robyn.
‫And if you don't know, this is our series and, and this Udemy course where we pick one of your questions,
‫they're fantastic questions.
‫All of the questions I get is amazing and I answer Titans at all of them to my best of their knowledge
‫and obviously time.
‫But for those questions that need some more time, I dedicate a lecture.
‫Yeah, like this.
‫So this comes from Robin Srivastava.
‫Let's read it and discuss.
‫Index on column with duplicate values.
‫I absolutely love that when you guys watch my lectures and then start thinking about these cases because
‫they are real world cases.
‫Right?
‫I never mentioned anything about duplicate values, but this is this comes to you, right, as you think
‫about it.
‫So stuck about it.
‫And this is this is how you basically you see yourself evolve, right, as you ask these questions.
‫That means you're penetrating and you're breaking these walls that the confusion and everything.
‫So is really the question if you are creating an index on column which can have duplicate values.
‫So it's a non unique index, right?
‫Then how is it stored in the index data structure?
‫Does the that is a doesn't maintain a key with a name as duplicate values, a list of rows or corresponding
‫page information?
‫Here's what I want you to think.
‫So this is for Robert and everyone here.
‫Really.
‫So the question, I don't think we need this screen anymore.
‫So let's go back to this and just talk through an excuse.
‫My hair needs a haircut, so.
‫The moment you find yourself thinking about how is it implemented?
‫Uh, don't worry too much about that.
‫What does that mean?
‫It means that.
‫Whatever.
‫If you find a way yourself, like don't worry about how big companies do it.
‫It's like everyone make mistakes, everyone implement something and then find out that it's not really
‫efficient.
‫Right?
‫So.
‫You provided ways here to solve this problem.
‫Work it out yourself.
‫If you think these are optimized and they are fine.
‫This is an implementation.
‫There is no longer right.
‫Yeah.
‫There is one implementation that could read the wrong result and that's obviously not applicable, but
‫there is implementation that is more efficient than the other.
‫So that being said, I don't want you guys to worry about much as like, Oh, if both of us do it this
‫way, this is the right way.
‫If my cycle is there, this is the right way.
‫No.
‫No, not at all.
‫From my 20 years of experience, though, what I have been seeing is always I'd be wanting about, oh,
‫if someone else implemented something that must be better than my idea.
‫No.
‫As you gain more experience, of course, if you're new to the course, you get to stumble upon things
‫that you've never seen before.
‫You might have ideas, but they might be shut down because you still don't have.
‫Expert exploded knowledge.
‫But the more you knowledge you get, the more confident you become about your implementation.
‫I think I got into the tangent, but I think it's a little bit valuable here to talk about.
‫But so the index duplicate.
‫So let's start with this.
‫If you have a column, right, and I don't know.
‫Gender.
‫Right.
‫What are the values in the gender column?
‫Male, female, you know, on a specified you don't want it to specified or you can have a little bit
‫of a spectrum there based on what do you want to date a model to be.
‫Let's say you have three values you.
‫The moment you have these three values.
‫All right.
‫If you have 100 million rows.
‫Indexing their role.
‫So indexing that column becomes useless.
‫Let's think about it.
‫Right.
‫Adding an index on that.
‫Becomes really if you're just searching for, I don't know, all the I don't know male who watches their
‫certain shows, right?
‫For example, a female that watches this certain show like a writer building a system for them and to
‫gain experience indexing that column, just that becomes really useless because.
‫Remember, there's something called index selectivity.
‫So index selectivity means you want the index to give you as small a result as possible.
‫So if you if you index this and I don't know, like 99% of the value is the same, then it's pointless,
‫right?
‫Because it didn't reduce your results and it's not selective enough.
‫And that's something to worry about.
‫So that's one thing.
‫You have what I want.
‫But sometimes there is duplicate values regardless.
‫Right.
‫But there's, like, more selectivity.
‫Like, so there's a bunch of duplicate values, like, say, categories, like a movie show category.
‫Right.
‫And then then that is, has more variety, right?
‫So the the value is becomes duplicated.
‫Sure.
‫But they won't be as dense as like if you have two or three distinct values.
‫Does that make sense?
‫That being said, you can combine that index with other things to make it more selective, like if you
‫have other indexes by itself.
‫That's what I mean by itself.
‫It's a bit useless if you have a lot of duplicate values with very distinct, small, distinct values.
‫So this solution here.
‫Now.
‫Now I'm going to answer Robert and question again.
‫I felt that whatever I said is the kind of relevant to this.
‫That's why I mentioned.
‫So to have duplicate values.
‫Yeah.
‫That's how I'm indexing column.
‫And think about something like project code.
‫Right.
‫And you have like ten codes, right?
‫So different products will have the same quotes, obviously.
‫Right.
‫Like, oh, that this is not unique.
‫This is like a code code as a category.
‫I mean.
‫Right.
‫So the code zero code wine code three code for up to code ten.
‫So let's say you have ten products with the same code zero.
‫So if you're indexed by the code.
‫Then how?
‫How does the index looks like without any?
‫We aren't being smart.
‫How do we do it.
‫Today in the B3 leaf.
‫Right.
‫I get you have to watch the three lectures to understand this.
‫But what we're going to do is.
‫The key becomes the code.
‫Right.
‫So code zero.
‫And then.
‫The value becomes zero.
‫Right.
‫So to say it all, one depends.
‫This is a primary or secondary key.
‫The value will be either a pointer to the primary key.
‫So that would be a primary key of their own or it would be a pointer to the battle itself if it's like
‫in case of progress, right?
‫If it's a secondary key or if it's a primary key that you're looking at, the now primary key cannot
‫be unique.
‫So forget about that.
‫So, yeah, so it would be the code and the row itself, right?
‫The overall idea which like 32 bits would depend on the size of the role.
‫And yeah, if it's prime at a key, then it's going to be like the key, primary key.
‫That's why you have to be careful.
‫What is your primary key here?
‫Because it will be duplicated right in this case.
‫So code zero becomes this right, this value wrote role one, right, let's say.
‫And the code zero again is allowed to encode zero or three and code 004 and goes 005 and go.
‫So that's how we're going to do it.
‫All right.
‫Obviously, if you think about it, you just duplicated the value zero many times.
‫So if you're indexing a 32 bit and you know, that's 32 bits, 32 bit, 32 bit, the two in the index.
‫So the index will be bloated.
‫Right.
‫So that's that's what we have today.
‫Right.
‫As a as a naive implementation, if you will, only rate.
‫This was implemented for the longest time.
‫It's only recently when PostgreSQL it's only recently when PostgreSQL, I suppose is 13 implemented
‫something called the deduplication in the B three index.
‫So that what they did is they d duplicated this so that in case of doing 0010020003 that had they would
‫put zero and then a specific array of the all the rows effectively that points to that.
‫Right.
‫Or based on what Robin said, just says, okay, let's put let's put just the page information.
‫Right.
‫That's why these are just right.
‫Let's just put the page information and then we can filter that when we go to the page.
‫Right.
‫That's another implementation.
‫So you can do any implementation as as long as it, you know, as you work it out and then kind of measure
‫the value here.
‫It really depends.
‫But all comes back to what the user wan with the DB actually doing.
‫Why are you indexing value?
‫So if you're indexing something with a lot of duplicates, really think about it really hard.
‫You know, what value does this give you?
‫Test it, you know, do the explain.
‫We have done thousands, you know, of these lectures.
‫Thousands.
‫I'm exaggerating a little bit, but we did a lot, right.
‫Talking about this almost as add an index.
‫See, does the explainer give you any of the above about our access path?
‫Right.
‫This is it's all at the end of the day, try and at or thinking about.
‫Okay.
‫Yeah.
‫While the theory is is sound, my use case might not be, you know, might not fit this case.
‫And that's basically what I'm going to leave it.
‫I hope you enjoyed this lecture.
‫I'm going to see you on the next one.
‫Enjoy the course.