Welcome to the Treehouse Community
Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.
Looking to learn something new?
Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.
Start your free trial
Leigh Maher
21,830 PointsDifficulty grasping the Index concept
When Andrew creates the index on the last_name column, and then does the EXPLAIN query, the rows shows 1 row. How does it know that there's only one row with Chalkely as the last_name if it doesn't look at the other rows to compare the last name entered?
7 Answers
andi mitre
Treehouse Guest TeacherAddressing your questions in order:
In an example where the table has very few rows, the index does become redundant or somewhat not needed.
Ideally, the index is used to avoid doing a full table scan think of this as searching for a specific subject in a book. You'd have to go through the whole book just to find the subject. Otherwise, with an index, you could flip to those pages faster because it is stored in memory.
Think of the last_name column index. Now if the result set returns one million rows, sorted. The engine can go to the middle of the list and ends up at say letter 'G' so it has gone too far but does not need to keep searching past G because we are looking for a last name starting in 'C'.
Hope that helps, for further assistance check out this link
Cheers
Ruxandra Chirea
12,764 PointsI honestly to God have no idea, but the way I understood it, I think it stores the data's location in memory. Like a map. Or an actual index of a library. That way, when you search for something, it doesn't start looking in the entire database, but it looks at the index, where the location is stored, and it goes directly to that location.
You don't look through all the shelves in search of a book, but you look at the index, and you go where it tells you to.
Uhm, I think. Can anyone correct me on this?
Leigh Maher
21,830 PointsThanks Ruxandra. That's a very helpful analogy. What really helped clarify this for me the most was this page provided by Andi above: http://stackoverflow.com/questions/3567981/how-do-mysql-indexes-work/29538483#29538483. In particular the comments by Pankaj katiyar. Very much worth a read.
Bryner Toma
25,541 PointsFound this link below. Fantastic explanation and visual representation. Scroll down to the section titled "Non integer types" for a paragraph relevant to this video for indexing VARCHAR.
John Valera
14,416 PointsWow that was really helpful in understanding how indexing works!
andi mitre
Treehouse Guest TeacherHey Leigh,
You ideally index on a column you plan to search or filter your data by.
He indexes the last_column and runs the explain command filtering by the last_name = 'Chalkely', in which the table returns 1 row because there's only one row with that specific last name. Hope that helps.
Cheers
Leigh Maher
21,830 PointsHi Andi,
Thanks for the answer. In the query he already specifies that the search should only look at the last_name column. Does this not make the index redundant?
The fact that he specifies the last_name column in the query, how does creating an index make the search any quicker?
And it's still not clear to me how the search engine knows, without looking at each entry in that column, which ones contain the last name of Chalkely? Surely, it has to check the entry for each field in that column to pull out the ones that have Chalkley as the last name?
Thanks.
Leigh Maher
21,830 PointsDamn. I'm back to not understanding this now, because it seems to me that having an index on a column is the same as searching on a specific column? In point 1 above you mention that having an index on a column avoids having to do a full table scan, but it seems to me if you restrict the search to a single column, that this does the exact same thing?
Ruxandra Chirea
12,764 PointsHappy to hear everything is clear for you now. And thank you for making things clear for me as well through your question and links :D.
Leigh Maher
21,830 PointsGreat. Thanks Ruxandra : )
Leigh Maher
21,830 PointsLeigh Maher
21,830 PointsOk. Thanks Andi. Those analogies helped a lot. Just to clarify: specifically on this last name index is it looking at the first letter C and jumping directly to C in the list, or is it looking at Ch or Cha or the whole name? What exactly is getting indexed? Is it always the first letter of the string?
andi mitre
Treehouse Guest Teacherandi mitre
Treehouse Guest TeacherSo the column is getting indexed not the values in the column. You can have an index on a column where the value is numeric say 1 or 2 or 3 and so on. In the letter analogy I just wanted to break it down so it would make more sense on how indexes work.
Kevin Faust
15,353 PointsKevin Faust
15,353 Pointsso basically indexing sorts out all the rows and then when you try to retrieve a row it uses the binary search algorithm