Voila, Indexes! A Look at Some Simple Preventative Magick

to the at the time and so on and and so on and so on and tell your story today by Wizard and Basil smuggling he styled himself a gentleman as well but his manservant can take tissue sometimes will quirky this is

Basil on the left ignore the similarity Mark Twain the the artist has issues but but that's the baby and there on the left they're currently vacationing in this hour tracking across the whole beautiful rippling golden sands or do not produce battles having a blast stadium not so happy about being 1 k luggage but when you the manservant that's kind of that's kind of how it works out now the other done all the touristy things they're there to to

see a bezels old school friend Ptolemy and so you know the the spend some time doing stuff to do things to a satirical comedy the portraits painted there is visit all the good restaurants sample the local cuisine is that all the markets found all the touristy the bubbles and somewhere along the way

Basil discovers an old brass lamp will let you know that I right super tarnished really ugly that'd be that which a buffer up a little bit that brings out a nice shine not only that it brings up a nice

gene that's a little bit cliche but a cliche that up to 3 wishes like this internet right Basil certainly wasn't that when you're a wizard you got a lot of you cannot power like what is a wizard going to wish for they can already conscious something on Basil things about this and it's a tough decision he spends a lot of time defined he remembers that that when he was a kid before he started dabbling in all this magic stuff he realized that there was a time when he was passionate about librarians he knew all the great library and binding effect he had all the trading cards is

Eratosthenes card was signed he was that hard work but the problem is that back then there were a lot of libraries there are few and far between and so we get the Peabody library and what your options you got a week for library the die or you know you could be often yourself and

make room but yet pre but the thing on sleep this phase that librarians are a couple of matches and the way it works the basil was neither patient enough to wait for 1 the dying or bloodthirsty enough to kill and so reluctantly reluctantly he followed his father's advice traded in his dream and his collection of cards for magic but nodal comes rushing back as the steering was gene in the eye he realizes but the thing he wants more than anything else is a

library so we wishes for 1 and only that reading a wizard things big so it's like I'll just 1 a library I want the biggest the most magnificent like moral wonder library that there is it's going to have more content than anyone else's Eratosthenes I'll from childhood no Eratosthenes is gone and the this thing he's he's serious and so to make sure you have more content than any other library he stipulates very specific condition to the Gini he says Jeannie what this library that for every minute but the sun is out I want 1 you scroll to appear in the library so every minute during the day and this girl she III whether synapsis fingers and blue and there's a library have basalis tho he is so excited about Fabian has never seen like this before basalis here's jumping up and down running in circles he sort side he runs inside the library immediately began writing out a schema describe how the data is all really account for the schools are can relate to each other and he's he's really excited now because he's excited in a hurry to these set you know minor details and we've all the right things that you know we can take years later so the guilt and that he rushes to get a schema

ready in this is what he comes up with you've got a bunch of tables that the he's got languages and nation's new people scrolls obviously and how they relate to subjects in and materials for each scroll he's he's really serious about this result has been a lot of time thinking about library these left something you got a no indexes the but is there a need of right not a big deal in this in this little the old world of imagination here he can go and grab a school really easy official it's not a big deal now how many of us have ever shipped a production table without putting indexes on yeah I mean we do how proud of it but it happens how many of you have lived to tell the tale me obviously here and those of you who aren't here obviously did not live to tell the tale and you are the fortunate ones so Basil to his chagrin is going to learn soon why you don't do this because what starts small the 1st

couple minutes it's if you scroll big deal but by lunchtime and he's got more than 200 scrolls on a shelf and by the time the Sun said that nite he's got more than 700 roles 700 scrolls mean slow was faces and the only way to satisfy the request there coming in because people of already heard about the world renowned library even in the 1st few minutes the common and again line saying I want this girl I want Aristotle and Plato right want I want I want want and so basil and Fabian both starting to have to answer these crazy new way to do it is to scroll by scroll

by scroll is that this 1 now is this 1 not not a good situation to be in because by the end of the next day there's more than 1500 scrolls by the end of the week we're talking 5 6 thousand scrolls on the shelves and has to scan the entire thing of this is exactly what happens in our database when we are foolish enough to leave often index our poor beleaguered database has to go row-by-row what we say give me any scroll by Aristotle that were database has to go ok this room is that this row is that this from this from which is not a big deal when you're in development and you have 10 rows in your database and when the 1st deploy your database the production before your users get their grubby little finger solving clean pristine data things run really fast too but as soon as the user's herein man these goes out and as you all internal you're sitting there 1 day doing just minus target alerts that your system slowing down you look at it like databases like working really hard what in the world is going on and people start panic requests come in in your work and your brains out is just not enough and pretty soon the whole system goes down ways of panic right ugly situation it this is the case here was just too

big of a job for basil and Fabian and alone couldn't do it I can keep up patrons were queuing up in they were starting a restless and I mean you think library to couple the couple for reason because the users are merciless these pages are lined up the line goes out the

door down the street and over the next 2 standard and no 1 wants to wait a line at long we still pages so they've had enough they torched the

library basil amphibian barely escaped with their lives are not yet give to battle he you can keep that out specimen ingenios into more wishes so since the will fire shy is that it would be to run well obviously we forgot indexes by with we had indexes it would all been OK because we would be number look up stuff and so Basil he's taking no chances he the

indexes all the things and if no index is a bad all the indexes have got to be better so you mean he's that indexes on like the ink color and he's get indexes on the word count of the scrolls he's taking no chances that this is gonna work so eagerly he jumps into the library

know how many of you remember card catalogs legacy of is good but to see so many hands going up a makes me feel less old base is card catalogs these great big enormous cabinets with is the same draws filled with thousands and thousands of cards you go to the other Cabinet find if you look Aristotle you pull opened a draw the thumb through tle find aid in Aristotle in alphabetical order and cards and then I would tell you all the different books that Aristotle had written that were existed in your collection pretty straightforward right same for title find a book by title you go to the title collection for subject you go to the subject collections I really not much to this is exactly what an index it's a sorted list of data that points 2 were something exists that's all indexes and so basils indexes were implemented this card catalogs and they enormous because member had to account for a new school coming in every minute and he was planning to be in the long run here an example of

what bevels title index might look like when the user came in and near the commencing on looking to kill medusa I need something that must be killer and 21 steps so bezels like again I would have 1 right here and it's over here on the shelf people that often queries answered in seconds super easy In fact doesn't begin together they have lots of downtime in the mornings especially within the slow scrolls commend you don't have to go on a few dozen slips them to file in all different continents but slow it's not a big deal yield entities grew so fast vessels like him so we know that this time he has a spear wish in the background like I think what you without later but sadly load is not always constant

in the mornings it's really slow and people come in for lunch still people wanted to come about this might reading during lunch and after work huge spike because people come in by the droves trying to get something to read for the evening and when you got this huge rush the last for an hour and you're working hard to answer the even once a fast query it still takes time and people are lining up they don't have time to deal with the scrolls that are coming in that's lower priority per serving requests right now and so the schools upon up at the end of an hour in the sixties girls that have piled up and each 1 has dozens of slips the people that work so suddenly they finish that rush now they get this huge backlog of work in a working through it patron there still come in and lower rate but those patrons are not having a wave of the index and it's just not not pretty on top of that patrons do ruin

scrolling sometimes will fall asleep in a Carolyn drool over scroll meet runs and that's less through the scroll out it's really tragic that about that's the the worst part when you run a scrolling have to go through it all those indexes pull the card rip it up and throw away it's a lot of work and on top of that the worst thing of all is spilled Fabian How do

you know what indexes to use my gosh I mean something like I need a book named effects will OK that's easy you go to the title index and use all the what some comes up and says you know i'd like a book by a Greek author about linguistics published more than 200 years ago in blue ink on yellow parchment and Fabian would just get the deer in the headlights look I have no idea where to even begin embezzled like it's the people handle the other 1 will take care of his children through trying to find the index of that so that all of the indexes to choose from and you know they did their best things are back in the Basel did his best

work through the backlog he summoned a small army of a monkey like these cute little things up to but you need would work like mad and can get a lot of the time that holy cow they have an appetite and they were eating does a lot of house and home they're breaking the bank he finally had to dismiss some before they totally ruined everything that that bottom a little bit of time but it was just the band in the end they did not have enough power process everything pigeons were lining up with again down the road up the hill over the dunes and again it worked on now basils

getting a little despairing at this point you can't blame them right and when I do not know the Fabian maybe that's going so much he used the basils escapades like this is part of the course pretty much being almost burned to death twice in a row it's kind of a bummer but he's like you whatever badly figured out in the middle of bells rankings bring OK no indexes that all the indexes of that what what we can you think about it if this is your database and indexes on everything you're in your database has to work really hard to write every time something comes in your

database has to go through and file something in every single 1 of those indexes that that correspond to and if there's a deletion ethical from Paul not there's an update the title of a scroll changes in the database has to go in and update every index that that is affected by that and every bit the work is a finite resource right we all know that every bit of work your databases doing not serving queries His work that it can't stand serving queries that sounds obvious but it's an important point the more work your database has to do that's not related to serving information for less work it can do serving information Basil learns this to his chagrin now as he staring at a pile of ashes that was once his pride and joy he

realizes OK I have this list like about 4 days worth your 2 days here 2 days they're forties worth of queries from from previous patrons that you can analyze the figure out you know maybe all the index that was a bad idea let's index just the things we need so he's like OK I can do that by looking through this data but this is a small snapshot of data like you didn't have the libraries open for very long before anything came crashing down how do you know the window the snapshot you have of your queries is sufficient to say what indexes you really need who's to say that the next day a patron would come in and query something holy that wasn't in here mean uneasy that was look at my title with look up by author with look up by nationality you know these different things but but what if the something else had you know and that is the crux of the problem how do you know what indexes your application needs it's not this it magic wand kind of a problem Basil had to rack his brain and it turns out that the key is a foreign 1 the I'm not at that but

the important thing here or foreign keys and better foreign keys were foreign

keys while foreign keys a column is a field in the database that refers to the field in the table is me that refers to a record in another table In this case we have scrolls that had an operating and we have people that are referenced by this baby so this girl has not there was the other table like foreign right external outside of that table foreign the foreign keys are a great place to start with your indexes knows makes sense we think

of in terms of braille's because of rails belongs to and has many correspond to these foreign keys you put belongs to on the child table the 1 that has the foreign key in it and you put has many on the parent table the 1 that is referenced by an so scroll belongs to an offer and a person has many scrolls and easy reciprocal relationship and Bingo there's a foreign key for it you can just look at all of your belongs to an older has many queries in building parameter foreign keys and that is a great place to start peerindex's but Due to index every foreign key on it depends

this look at this example where we have some the a person and nation to model right a person belongs to a nation in the nation have many people is another person donation that's just that's not really using foreign key right it's taking the value in the nation ID and then doing a lookup by ID in the nation's table and since we get a primary key indexes for free men indexes on ID that's that's a really helping us a lot got that 1 down and but the other 1 if use a nation that people give me all the people in Germany in all of the people in China that that's going to go through that's gonna look at the nation ID column the people table but is that a useful query where you going to do with millions of rows from a single query you're gonna crashes which you're going to do that's not that's not very helpful thing and so that may not be the kind of query you want to do maybe you don't need the index nation I mean in that case maybe and we'll see we'll see about coming out of case but but consider the direction of your queries in order to determine which foreign keys you really need the index because you might not need index them and another 1 rails that's

great has many through also has a in right this is the the type of query that gives you 2 models for the joint table in the middle and let you do you know the many-to-many kind of

relationship for a subject has many scrolls and scroll has many subjects all through that intermediate joined table now you look at this new and OK so where is the

foreign key in this 1 with belongs to and what has many the the foreign key exist on the child table but where's the child table in this case right that's not strong it's not subject that jointly in the middle it has from the foreign keys on it has the scroll ID and it has the subject ID on right to Unix both unit depends with they look at us

scroll has many classifications that's that's central cable that connects subject the scrolls the school has many classifications and it has the subjects through that classification stable if you're going to say scroll thought subjects it's going to go through the classifications people find every classification or the scroll ID matches the idea of the scroll so at that point yet scroll ideas when you 1 index for a query going this direction and when the other direction and it's the other way right this time we have a scroll with many class per subject with many classifications and many scrolls through that classification stable and so the unified classification by subject now you look at that subject ID column that's the point you're going index that now does this case he's looking at it is like the people are gonna wanna know what subject to scroll as and people are gonna wanna know which scrolls belong to a particular subject so all indexable fully reasonable right hot baby and now he thinks he's

caught his master out this never happened so Fabians breach of about it he's thinking OK hi him only have 1 of the fact that the master you know you well you say that we need to cut down the number of indexes and then you go ahead and you create 2 indexes can't you just do 1 with 2 columns the not the same thing and you just put an index on school ID and subject ID and get the same result and here we have this Fabian because no

that's not the same thing at all remember an index of the sorted list data but when you have more than 1 call that data is sorted 1st by the 1st column then by the 2nd column and then the 3rd column in the 4th and how many columns you have in your next so in this case we had scroll ID subject ID you could totally look something up by scroll here right because it sort you could go in and say OK School number 1 thousand 27 they have been fine nanoring you find exactly which subjects correspond to but this some patriotic come along and say OK I need everything corresponding to the subject number 7 the 2nd column is not sorted by subject ID and so it's not gonna help you want that you'd have to resort to a full tables can't answer that a a specific so far KB you have the index both of them if you want the two-way query support circadian vital right panel what is the

point of a multicolumn index right is not saving you anything why use it so when you Fabian

because that's the very next slide yeah let's say members said people that nation was a bad idea because it could return per nation of people with a bad idea because it could return millions and millions of rows well you combine and index with another column and suddenly becomes much more useful the 2nd column acts to refine the query so you say nation versus can be sort of a nation states OK 1 find all the Greek offers and Aristotle state the database as a goes out and says OK we go Greece Greece there is are scholars scholars Pollock that however many happen to be in the very fast query and so that that index that may be was useful by itself now suddenly it's useful when we yeah combine it with another problem the water matter as a matter of that we put nation 1st and and full 1st and 2nd I am not not particularly but if we were to swap those we might be bit to useful indexes for the price of 1 is if we swap and now we can answer questions like any at all people into Plato that's Plato the philosopher and not the only right we can find all them just like that regardless of where they're from or we can say need all people link play-doh from Greece the and we can answer that greeted so with a little bit of not keeping engineer your indexes to satisfy multiple types of queries and this this kind of multicolumn thing is especially useful when you're dealing with data that

is continuous as opposed to discrete discrete like ideas 1 2 3 4 5 right vs continuous like a time value you're not going to go in and say OK if we had a published at column on a scrolls rocket never get have some come and say I need to every school that was published on the 23rd of April 30 ABC and 942 area you never going have that because it's too specific and it's not the kind of information people keep their heads about the kind of school they're interested in and however you add

authority that sorting by operating 1st and then publish that you can answer queries like I need every scroll published by every school by this author in order of publication date and your index give that to you because you're indexes are just sorted lists yeah and in fact when it uses an index like that many times that I think it will even return the data in that order whether or not you will provide an order by which is a nice interesting side effect and another example of

this here we have a little query that's going to say give me all you depend most recently added scrolls the schools that were added most recently to the collection notice there is no criteria here there's nowhere costs were not thinking of schools where this is the case were to saying give me the 10 most recent scrolls now without an index on added on what is your database can have to do it's going to hurt but at that index is going to have to basically reorder the entire table just so can return the 10 rows that correspond to the most recent not a happy thing to do when you got a million rows of the data but if you have an index on that so the that query becomes trivial because all the data it has to do in this case we're we're sorting ascending rightsholder Davis test is good or very end of the index and a train the kind of the bottom and you got the 10 most recently added go just like that so please don't do like like basil did at the 1st right and and forget your index is because indexes on sorted on sort columns are as important as promote anything to and make sure that you're doing sorts in the database that you're indexing constipating like OK 1 more question as

hand were so close stop arebeing inside well but how do you know that these indexes are actually going to be used how can you know that the queries you're going to be giving are actually going to be satisfied by these indexes but the heads pressure vessel thinks about that for a minute he's like you know from the check my stylebook Endsley flip through a little bit looks to be the index in the back and he stumbled upon 1 of a special little incantations called explain and explain his an incantation everyone in this room can and should learn it's really not hard to master a stable again I worked it would the query that says give

me all the scrolls by greek offers right it joins on the author and the nation table and find all the nations Greece and and returned all the matching not too bad especially given index but now it's a we want to see what indexes in fact how the database in general is going to satisfy this query rate for this but use but not explain on the Internet and rails on run the explain now will side effect of this rails will actually execute the query 1st and then return the explained I'm at an entirely understand the need to understand the rationale behind and if that's a problem like if your query is 1 that's gonna take like 3 hours to run then you might want to go directly to a database consul and key in the explain yourself but this is a quick way to get familiar with how they explain works if we were run this explain and without indexes it would stand out something like this

now this is really arcane output and very typical of the kind of thing that 3 and this is my C but my to output as well as it can be different depending on the database using a list quickly and on the left over see there's 3 rows in the upper I have attended the break the table and have as so long so bear with me on that the the top is the the left side of the table on the bottom of the right side of Table so scrolls the there the same 1st thing i'm gonna do is I'm gonna look at schools table and under the type column see that were all in all caps that is bad news that is my SQL saying I have no other recourse but the scan the entire table row by row and the marginals after that we'll or those in a minute but it's saying the rows columns on the bottom there is saying I expect we're going to look at about 43 thousand that number could be much much higher if that's the number of rows in table and you got 4 million schools in your database that's the mean can say as a rule of 1 billion euros and the next OK for each school that we see were next we'll look at people table and this time I have a need to look at because the primary key index is given for you for free so so they can use that 1 and then use the scrolls got author reference for foreign key and I estimated there's going to be 1 matching called 1 matching record which you'd expect because each scroll has 1 and then it says from that men were going to join on the nation's table so again we can use the primary key and we're going to look at the people that nation ID compare that with the nation ID on the nation's table and I estimated in 1 row there so for every single row in the school column there is the same thing in person gets it the nation the nation Greece nope brought out of the against role people nation yet match keep that over and over again for the entire world people and that is what kills performance so once we have indexes though suddenly the query plan

changes notice that's not even starting with scrolls for now the Davis basis saying a hop if I start with the nation's provoked this is give me a lot more efficient were going it now it's suddenly saying oldest possible keys use here I have a primary key and I have this nation's name he and I think will put the nation's name he because that lets use the name of I it's the 1st estimates that there will be 1 matching row and it's using where and using index using index is a lovely sound because that means that the database is going to be looking at the index the solid and it can actually use just the index doesn't have to go to the data on this the using where so that's actually comparing the information in the where clause to the index of a to come up with so it finds every nation may increase and then for every nation finds there it's going to use the people table and here to using the people nation index because now going the other action right it can use this by using the primary key is not looking at people by nation IT and again it says I as the final 1 the it sometimes it's just an estimate to but using where using index um and then finally scrolls and look of schools by authority and result comes out the Presto and thinkin about 107 rover enough to look at you and that is a whole lot better than 40 thousand so see how explain can help you answer these questions like why is this query going so slow that you can look at it and see all haricots wonderful table scan on this this table that used to have just a few but now has thousands and thousands of rows useful will no so this is about the new

schema is ripped out when she was dozens of indexes but he doesn't need so significantly slimming down as this schema and he's ready to try again he had some of the Genie makes of wish

presto there's a brand new library and he gets to work scroll start coming in 1 minute he's going up just a few indexes now for each 1 in Figure answering the queries few hours go by by their nails it seems to be working they're actually staying on top of it this time queries come in the handling it just fine every once in a while you get the word

it's like you know I need every Egyptian scroll they and about the geometry that was written between the 5th and 8th century BCE and needs to be in blue ink on yellow arch and they're like you go way over there have time to deal with you right now the next and then anarchy dealing with another person angles weights over in the corner and tell you have time to deal with them which is usually gonna be at the library closes the scrolls that come in and things are commonly go in and do is leave 1 out of your queries these authority like 1 2 maybe 3 of each day so it's pulling tool sadly and who knew that Alexandrians have this thing about libraries and so on general principle the torched

but embezzled baby and you know vessels pretty crashed they whatever I was cut I must die again that's part word Melville the basil is learned that he can do this is found system this can work is gonna go now someplace where they will appreciate his libraries and not bring them down on general principle and he is going to be happy and so

now comes back to you how are your databases looking what's the health of your database how long you been treating your database what indexes are you missing what can you do to increase the speed with which you can respond to customer queries look at your foreign keys look at the belongs to has many have many through look at your sorting see what Kolontár sorting use explained to figure out whether or not your queries are actually using indexes that a lot better to discover that your queries on using indexes when the load is below like when you're query still see that's the time to be checking to see whether or not they're using indexes could you don't wanna wait until suddenly these queries are taking for 5 seconds to do in the requester backing up because what you do that an index of the migration and migration that an index the full table scan which is not going to help the situation 1 of them the to take my advice and the indexes as soon as you can in it that means adding them at the moment you you create a migration for the table please do that is the way to go so if you've enjoyed basil and

Fabian at all last summer I wrote a little Novell online and blog that gene part of a word um uh grid you read it has nothing to with databases it's about them discovering path-finding algorithms and Anatolian related to this at all that because I'm like I had the opportunity to do shameless plug and also writing a book about maize algorithms for programmers basis for programmers that sounds interesting you all you need to prep product concept book by JB me and it's India that idea at all of you are right now but a few few chain so these men in this the so this is a GA and so on and so on so users is and so e g and uses some