Alberto Schiabel: Supercharge Your SQL with Indexes: a Practical Introduction

In this session, Alberto Shyo provides a comprehensive introduction to SQL indexes, focusing on their crucial role in optimizing query performance. He explains that indexes are data structures that speed up data retrieval by allowing faster lookup times compared to scanning entire tables. Using MySQL and InnoDB as examples, he discusses various types of indexes, such as primary and secondary indexes, and how to determine when to create them based on query patterns. Alberto illustrates the mechanics behind indexes with practical analogies, such as navigating a hotel’s rooms, and delves into the effects of different index types on query execution times. He emphasizes the importance of selecting the right indexes for specific queries and offers insights on managing them effectively in a production environment. The talk also covers the use of generated columns for indexing JSON data, illustrating how structure can enhance query efficiency.

[Music] ladies and Gentlemen please welcome our next speaker Alberto shyo presenting the topic supercharge your SQL with indexes a practical introduction but I was a speaker here last year uh online and this time I'm going to talk about SQL databases and in particular about indices and why should you care about indices well uh they tend to make your queries fast uh today I want to give you

an outline a general I want to give you an idea what you can do with them what differences there are and what are the most important things that you need to be aware of uh when dealing with them because there are they can be quite tricky we're going to use my SQL and inot B as an example and but most of the information we're going to share

today are portable to other databases as well and my hope is that even though you may be a very knowledgeable person on the topic hopefully you will uh bring home some new information from this talk and who am I I'm Alberto skel I'm from Venice Italy uh I'm been a software engineer a consultant a former startup uh co-founder currently I'm mostly working with SQL uh rust typescript

and web assembly and most notably I'm one of the maintainers of the Prisma um opur project it's basically our Norm mostly for T developers and one cool thing uh we do is we take an access in database we pull all the information about it like the database schema and we Port it into something that we called a Prisma schema that we use to generate typ safe uh

queries types safe objects and methods uh for uh other languages and we do so um without you having to care about the spe specific SQL dialect of the underlying database and one of the information We Gather is the kind of indexes that the database has but what is an index let's go through an analogy maybe all right imagine you are a speaker here at that days you

traveled and you arrived here in vus and then you booked a hotel what happens when you go to the hotel well there is a a guy or a person at the reception it's going to hand you the keys and going to tell you hey this is the room where you need to go to but the hotel has several rooms and floors so how do you fight the

right one do you go through every door imagine you have like uh 1,000 rooms uh how much time are you going to spend looking for your own room probably you're gonna be smarter about it and follow the signs you're going to have some signs spread around the hotel that will allow you to quickly find your way to your own place you're going to have B basically going

to tell you hey you have a set of rooms on this direction a set of rooms on these other directions usually they're also kind of sorted to make sense uh or where you need to go and in this analogy the hotel is is basically a database table and the room is one column of database that you have an index for because these indexes are essentially directions they

uh allow you to quickly go to the data you need so in practice what are indexes um in the general sense they are a data structure like um you might be familiar with primary Keys already they are a special kind of index because they are an IND that contains the entire data but most generally an index is just an additional data structure that is separate from your

data it contains a portion like a sum copy and you use that to go back to your original row data there are several kinds uh like the major distinguish uh the major thing we can distinguish it is we have a primary key which is like a unique identifier for every table and then you have possibly many many uh secondary index which can use to we can use

to refine our own queries we going to see much more uh later and why are they important well simply for performance like they can make a difference between a 10sec query and a 2 millisecond query for instance uh if you run an application in Ser of many clients your application is only going to be as fast as your slowest query right so you really want to um

to tune your queries and try to make sense of uh and make use of everything that is available to you and sometimes a linear scan uh like a a linear scan over all your entries in the table that's just too much if you have like millions of billions of rows you don't want to wait that um when should you create an index you can just create them

every time you want there are some constraints there are some things to keep in Balance so the the obvious answer is it depends um the idea is that you need to create as many indexes as you need but only as few as you can get away with and usually there are some Clues um when you look at your queries there are some expressions like SQL blocks that

you that probably will need an index when you use order buy when you use where when you use group buy for instance the columns that you mention afterwards in many cases that might benefit from having an index of course you need to measure that and there are some tools at your disposals like you can just look at the indexes that exist for a certain table or you

can even uh ask database to tell you what it plans to do with a certain query with the explain command and usually when you create your database uh schema you start from the data at your disposal or like from the data you expect to gather from an application but for indexes you can just look at the data it's not going to tell you much uh about how

to speed things up you need to look at your queries uh you need to look at your um data access entries right so if we go from data to schema we need to go from queries to infer and to basically build our indexes up uh from the queries we expect to run and what does my SQL documentation tell us about indexes like when uh what happens if

we don't have any index well without an index my SQL needs to begin from the first row of a table then read through essentially the entire table and then filter uh irrelevant Ro go in between the larger the table the more this costs right we want to avoid all of this let's look at an example here's a uh a column of a users table we are indexing

we see we have like uh three different values uh on the left side we see an ID that will be the primary key it's going to we're going to expand on that later and imagine we want to run this query we want to select all the users where the first name is Alberto last name is scal and the users need to be active what happens when you

have an index the database first goes through this index data structure you see here we have a where first name equals Alberto it's going to use that index so it's going to uh pre select essentially those three entries on the left side then from there remember an index has a pointer to uh basically where the uh rows the rest of the rows and the rest of the

columns are um Britain so it's going to point to the table and rather than scanning the entire table and Fally the things we need it's going to just scan a portion of that table just the portion that was index before and that's kind of the beauty of it from there you can apply the actual FAL logic like you can refine because the index usually only covers a

small portion of your query but that that should be like just enough to get to buy and uh make the query faster but that's the just essentially and usually uh in the B you have too many data structures I'm just going to cover one uh for today that will be a b tree like a balanced tree where you have more than one note per level and the

the cool part is pretty much every database uses this data data structure it's been invented in the 70s and everybody was like okay this is amazing we're going to use them for like the next 50 years and they are still quite relevant today uh they also use a similar version of them uh just for space queries we're not going to bother with that today uh here's an

example of a B plus three so you have like uh you start with one route and then you have like you see each note can have multiple children um on the rose we see uh ranges of ideas remember with the hotel uh analogy where you have the signs it tells you like room 4001 to 410 are on the left or 411 to 420 on the right that's

the same thing and we have a single rout at at the top that's where every search starts from there we have we can have multiple intermediate levels on a typical database you might have like three or four intermediate levels and then you have the leaf notes at the bottom and one thing is um that's where the keys for the index are are stored in and they are

stored in alphabetical order like a lexicographical order these enables for instance for very fast uh read ordered read of the uh index color and in there since you don't have like other notes the tree doesn't split any longer you can just use binary search to find the exact column you need uh and overall the time is still going to be logarithmic rather than linear so we avoid

the full um the full T of scan and notice how every lift node is connected to the other right this enabl scanning from one to the next one and LIF noes are particularly important like in primary keys they actually contain the whole data for your table so not only you have the column you have a pointer to every uh column for that specific record whereas in secondary

indexes which is essentially any index that is not the primary key you're going to have just a pointer to the primary key so at the end of the day what matters is that every secondary index is going to have the primary key appended to it and this like from the space perspective from the cost of adding an index to your database into your table you need to

try to have the smallest possible primary keys because they're going to the larger they are the more expensive the quer is and the storage is going to be and what happens with a search let's try to do an example we are looking for 44 we start at the top we go through we look at the children we might have every children tells us what range of uh

keys they have which chose the appropriate one if we at the intermediate level we continue at some point we reach the bottom reach the LIF node and that's where we can just apply bner search to find the right column we were looking for what operators and like what kind of operations can we uh benefit from in a query well if you have an index column with a

B Tree in particular you might use equality you might use uh comparisons like uh less than equal bigger than equal um between for dates and then you might have some level of string matching one important thing is that since these bories they are from a unique route right uh you can only uh query prefixes so essentially if you have a like expression like give me all the

columns that start with ab and then they have um a limited number of characters and then they have a d character you can do that as long as you basically benefit from the ab part of the string in the index and the rest is going to be disc started the database is going to need to work uh double for that but you can't for instance look for

something that ends with a a special string and also inequality operators generally they do not benefit from an index and like there's an example we can look at so first of all what kind of indexes do we have in in our table this is just a users table we have a primary key and then we have a bunch of unique indexes we're not care right now if

we take a look at the query from before like all the users where the first name is BTO last name is gial and the user is active we're going to find one but did we use an index the explain commments tells us that no index was used how can we tell we need to look at the possible keys and the key uh column possible keys are the

possible indexes that were considered by the database for this query and the key was the actual index that was used in this case the key is null no index was uh but if we look for something using the primary key then yeah we're going to see that the index was used we see here key is primary let's create a simple index the simplest possible index uh for

instance on the first name all right that will take a little bit and now if we try our query again just explain we see that this index was taken into consideration and it was also Al used and about the operators we were looking at let's try for instance to find all the users where the is between 1,000 and 1100 right those are a lot it took just

a few milliseconds 9 milliseconds what kind of strategy did the database use it looked at the primary key we see here um what if we want to for instance look um at the birthday at a special birthday so we add an index on it we select here we're using the between operator which is supported by B plus 3es and if you look at the explain statement it

tells us okay right the and the index was only like three by long and we we made use of it it was very fast if we drop the index this might not be as fast just took a few more milliseconds uh if you have like more data you will see notice that much more and what if we look for queries that start with a certain um string

can we benefit from an index we do we have an index on the first name and we are using it here key is idx first name we're using it but what if uh for instance we're looking for something that ends with a certain string and we don't know how it starts as we see uh no possible indexes were considered uh this is not really possible uh out

of the box so that's something to keep in mind whenever you are indexing a string for instance now we talked about primary Keys most of you probably know already what a primary key is is but uh a few things to keep in mind first of all it's non-nullable it need to uniquely identify your um an entry in your table uh they a primary key again is copied

to every secondary index so the smallest the primary key the better because otherwise you're going to your secondary keys are going to be very very large and when you have several insertions right usually in a normal app you may have several reads but at some point you can also have rights to your uh database every new insertion is going to cause the tree to rebalance the B

tree needs to keep in Balance otherwise you have cases in which uh basically looking at one brand is going to take a linear scan and we don't want that certain depending on the type of key you use you might have those situations so we're going to see how to avoid it and also you might you might run out of room if you have a primary key who's

um where the number of bits is too small basically where you can only represent a small set of values then at some point you might run out of room and it will be difficult for you to migrate to a larger key space afterwards because when you think about that problem it's always when you're like very deep in a uh maybe you're dealing with a production uh bag

right with a production outage and that's like the worst timing possible for thinking about how to expand your key space for the primary key so maybe let's think about it beforehand so like this simple solution that I would recommend is using just begins especially unsigned begins with the auto increment um keyword they have the cool property that they are only 8 byte long but you can represent

essentially two to the six power of 64 values that's a lot like those are billions of and they have they are very simple to explain they are monotonic so basically every new entry is going to create a uh it's going to bump the previous index by uh you're going to they are very much performant but also they might have some constraints or like some problems for you

if you expose your ID to any user maybe in a API or in the URL of your application then they might be predictable that might be a problem in some cases and the sequence essentially that generates those Auto incremented IDs is database dependent if you migrate to another database you might have problem synchronizing those uh in most cases you might you probably won't need that uh you

won't bump into that problem but perhaps you will and also there are um two to the power 64 bits might not be enough for you if you are I don't know a banking application if you are working at Uber Facebook so there are other Alternatives like uu IDs probably you already um heard about them there are typically just 128bit strings uh you can represent them as a

36 bit uh uh hex decimal string and then you have like this very nice example you see some of the bits are reserved for like a version in a variant but still you have like 122 bits available and all of them are random so you might have some Collision but uh you might also you have a much larger key space so that might be more uh beneficial

for certain kind of apps they can also be represented in binary and that would be ideal because you can spare some space essentially there would only be double the size of a unsigned begin and I would recommend that uh most databases have like a a data type specifically for this but you can also use just binary 16 and an alternative to that will be the UL ID

which is like the universally unique lexicographically sortable identifier so one thing we mentioned before whenever we insert a new row we need to be careful about the rebalancing of the B+ tree if if we use sorted Keys like monotonic Keys such as the Auto incremented ID or this kind of thing where you always have a time stamp at the beginning then they're going to be naturally sorted

this will minimize the need for rebalancing if you use a u ID where most of the bits are random both at the beginning and the end then you're going to have many more rebalances it's going to be slower to index and to insert things but you all ideas of also uh have a bit more space you use the full 128 bit and they are very precise like

you can't have any um Collision within one millisecond they still have some problems though and one problem is well before the problems uh they in Tex form they are smaller than the uls so that's nice they only 26 bytes rather than uh 36 and if you represent them in binary form they are the same size 16 bytes um what's the catch sometimes they um you might have

several microservices and they might have a new insert request at the same time and if that's happening at the same millisecond and they have the same uh random seed then you might have a collision there and you might need to retry the insertion and then have some sort of like backoff mechanism whenever you inserting things uh also like begins you mostly generate them from the database themselves

so you don't pass an from your application to the database where whereas with u IDs U IDs most likely you're going to generate the ID from your application code and then going to insert it to the database rather than generating it directly from the database another cool part about uid is that they are your friendly like they don't have some characters that you might confuse whenever you

display them on a URL like I and L you only have one of them um you might there are also other Alternatives where you just use a more compact alphabet and like you have fewer strings available smaller in size like N8 but you get the idea um I wanted to show you maybe some example about this like I already have a bunch of ideas uh generated for

this table uh I don't know if I can make the results here larger but just to make an ex an example here this will be the uid in text this will be the UL ID in text and also like there are things in binary form and we can take a look at how much they cost in size so if we go through our table we see that

the key that costs the most in terms of size is the primary key remember it holds not only the B Tre but also the entire data that's like 115 megabytes we have like half a million records and then if we see the UL ID and The UU ID they weigh around 29 megabytes because they have the same size almost the same size uh uh the U ID

in binary form that would be around 60 megabytes so and if you have just the non with a smaller character set you going to have like 50 megabytes remember always try to have like the smallest uh size possible and you can just um track them and keep them in mind whenever you experiment with keys or whenever you experiment with indexes for that matter try not to add

them and drop them continuously first of all if you are in production uh adding or you know reshuffling the keys may cause outages because you're essentially locking certain tables or large portions of your tables uh what you want to do is either to apply them in the background or maybe on a tier 2 database or you might just to um toggle them for any ID or for

any uh index you can turn them on or off with this alter table table alter index the name of the index then you say do you want it to be invisible or do you visible and then if you take a look at again you see here there's a column visible and many of them are not visible anymore right back to the slides about secondary indexes so again

those are any index that is not the primary index and you might already be familiar with unique indexes and foreign key indexes as well as unique constraints and foreign key constraints while the in my SQL a foreign key constraint will automatically create an index for the key you're referencing uh to a like a an external table and for Unique Keys as well um keep in mind if

you use posters for instance a forign key constraint doesn't imply having an index under the hood so if you run join queries with po you might not benefit from an index but in my SQL you always do and again uh what if you have a primary key and then you also have a unique key and they Point like they index the same column what do you think

will happen so ideally you would imagine my SQL would be smart enough to figure out they reference the same table they have the same essentially the same data under the hood so just don't create a new one you know the primary key is also unique so just don't create a new one my SQL is not that smart uh for good reason because it needs to be very

fast at elaborating uh performance plans so it's up to you to ensure that you don't have redundant indexes and again show indexes is your friend all right so we talked about creating and maybe toggling on and off indexes but how does my SQL use the indexes we create for it my SQL chooses the most selective index available and to understand that we need to intr ruce the

concepts of cardinality and selectivity so first of all you have one big constraint you might have any number of index in your table but my SQL is only going to consider one per query so this need to be very fast at selecting the best one for it in most cases and it's going to select the index that is expected to contain the fewest rows so the least

number of rows why is that because you want to filter as much as possible within the index before going back to the table to like the filter version of the table and apply further filters and groups and orders within there so it kind of makes sense and every index if we take a look at uh for here I think the the show index was the one like

every index uh keeps track of cardinality here you have a thing called cardinality it's essentially the same as the count of unique values for that column within the table but it's not exact it's just some sort of statistic and they my SQ uses that to in infer how many entries am I going to expect with that particular value and based on that it selects the most uh

selected one so the cinity just the number of dis of uh like the number of distinct values in a column and the stivity just the cardinality divided by the total number of rows in the table and one question for you to see uh if this is clear is what is the selectivity of a primary key I hear one does everybody agree we can quickly check that one

moment here we go all right so this is uh let's do this C rid of this this this is the this is it this is the primary key just the autoincrement ID this is the cardinality this is the selectivity and we see the value is one which also brings us to our next topic what if rather than indexing the entire value which might be a string it

might be a very long string it might be a blob it might be a text it might be a varer what if indexing all of that was too painful too costly for us could we get away with just a portion of the of the string and in particular we get away with a portion portion of the prefix of the string well we do uh prefix index is

exist for a reason and indexing long strings is a really bad idea because at some point uh the data structure is going to be so slow uh to deal with it uh that my SQ is going to give up and just avoid using the index in the first place and just go with a full table scan or to choose another index uh so let's take a look

at how what we can do imagine we have a column where we know that some values at the beginning they are repeated right and if we find them then most likely the the we are not left with many values to filter from and one example would be The UU IDs or the UL IDs and here is an example for instance with the U ID uh ID these

are the ones in textual form and we're going to calculate the selectivity of essentially the prefix starting from uh considering only the first eight characters up to the first 11 characters and we know that this ID for instance is unique so it's going to have a selectivity of one and we want to find rather than um indexing the whole 26 characters can we get away with a

smaller number of characters to index so if we query this all right we have it originally we have the selectivity one if we only consider eight characters we have 0.002 as a selectivity that is very bad it means that if we index the first eight characters it's going to be useless pointless it's not even going to be used if we index the first nine characters we have

a selectivity of 0.037 that's better but not quite enough if we index one character more 10 character ctors we're going to have a selectivity of almost 0.9 that is very high and that would be probably enough and if we had even one more uh character we going to have a selectivity of 0 9969 so what can we get away uh like what do we infer from this

rather than having an index covering the whole string that is 26 bytes we can just get away with the first 10 bytes how do we do that we alter the table we add an index and when we uh mentioned the index uh the column name we open the parenthesis we specify just the number of bytes that we want to consider or actually the number of characters and

if we do that and we also evaluate how um how much these indexes are costing on disk we can see that this the index we just created is only like 13 megabytes whereas the index on the uid uh C like the whole thing is more than double it's 28 megab so we see we can benefit from these kind of things uh and this especially applies to whenever

you're looking for Val columns that are hashes IDs IDs or maybe you have a if you have like a search with a typ Ahad in your application and you queries usually start with a you know users write something they expect an not to completion afterwards and you don't need like crazy text inference uh apis then you might get away with just a prefix index it's going to

make your queries faster what about postfix what if uh remember my SQL is not really made for that but there are a few adjustments you can make you can still rely on prefix indexes if you maybe generate a column that is just the original column in Reverse form right so the prefix of the reverse column is just the postfix of the original column or you might use

a more advanced uh structure called the full text search index uh I recommend you to take a look at that we're not going to go through it right now but also you might try to uh in a specific domain like if you have an email of a user and you want to query the domain of uh like the most uh the number of unique domains you have

uh for the the users in your system you might just extract anything that come after the add sign or that string you place that in a generated column and then you just um apply an index to it and that can be quite fast for instance for if you want to uh if you want to if you have this kind of questions in your database so far we

only seen a single index or like a single index using a single column can we do more can we use maybe multiple columns at once and if so what happens how does my SQL react yes we can there are some things to be aware of uh the idea is that you can have up to 16 columns per index and whenever you declare them essentially just have uh

you declare the index name and then all the columns and it's just a list that list is considered as a sequential array so essentially essentially the position of the column within the index matters and it matters a lot in queries my SQL is only use able to use the indexes if the columns are basically accessed in the same order in which they are declared in a multiple

index and the idea is that you just go left to right uh it doesn't necessarily mean that you need to declare them left to right in the queries themselves it just means that if you have certain filters and for instance you uh you need the column number one and the column number two you can just omit column number one and expect uh the index to kick in

this is much easier to be observed in example we have this query here we're looking at the users where the birthday essentially between in December uh 1997 the last name starts with SC and the users need to be active we have a bunch of them uh if we run the explain query we see we have no possible Keys No possible indexes uh for this kind of query

what happens if we create a new multi-index using the first name the last name and the birth date we create one it takes a little bit if we rerun the query and like the explain query again nothing really happens why is that because we are not using the here like if we use the first name and perhaps we select my name now we have one possible index

to be used actually we have more because we have one index on the first name alone and one index on the um first name last name and birthday but my SQL chooses this first name last name and birthday why because it has a uh higher selectivity if I get rid of birthday here this is still going to work fine with that index why is that because basically

we're filtering by first name and then by last name we didn't use the birthday part of the index but that doesn't really matter and from a visual level it's kind of like this the index now has more than one column in this case just two we still want to retrieve just a s query as right by the way uh notice how um yeah uh we we are

uh ordering by first name and then if possible we're also ordering by last name but the first name order is more important we applied the filter beforehand so essentially the only actual work we're going to do on the table is going to involve IDs 21 and 23 from there we're going to fetch the other columns that we need for instance the is active so we're just going

to apply a a search in two rows rather than five in this example and notice that it always begins with the First Column we declared first we we filter there then we filter on the second column and so on and once we have the whole set in there we apply the rest of the conditional logic that SQL requires yeah and finally you may Wonder All right so

can we apply I don't know partial index or multiple column index to unstructured data such as Json many of you probably know that postare supports Jon but I was surprised to know that my SQL also supports it um it does but you can't really do much by default so what happens for instance if you have a table like follows you have an ID and you have a

data the data column is just a Json it's not nullable but it's Json you don't really know what kind of keys to expect in there what if you want to add an index in it we're going to have um we're going to have an error and actually let's see that firstand I don't want you to just trust me on that I have an error here it says

that the Json column data supports indexing only via generated columns on a specified Json path this also implies that if we try to run a prefix index like on the initial part of the Json blob that's just not going to work it's going to be useless and what this means to uh what does it mean to have a um specified Jon path essentially you need to select

one key from that Json so first of all you want to take a look at it perhaps just the first few entries you realize it as a certain structure here and some keys are repeated and maybe you want to uh maybe they are repeated like the same value appears more than once and you may want to uh perform queries on it like or maybe aggregations on it

for instance we have some types these are these are uh sensor data we have some weather information we have some temperature information pressure information and we might also have IDs like the U unique IDs from the machines that are sending us this data we know that the maximum size they have is 36 so perhaps we can just create a new generated column that matches that uid data

from uh maybe the synex is a bit tricky to explain this just says start from the uh column that is a Json and extract the first key basically dollar. ued that will be dollar column right and if you that and you start querying the data now you see you have an additional column here this is additional uu ID and you can you apply anything to it well

if you run something we don't have any ID on it we don't have any uh index on it but can well we could create for instance an index a prefix index on it remember this column didn't exist before and now if we perform this query where we're filtering by a specific uu ID that didn't exist in the original data now we see that we are considering using

the index and we're actually making use of it and this can make especially when you have a large amount of Jone this can make things so much faster um what do we want you to remember from today we looked at several different uh things about index is first of all indexes matter for performance uh but don't get too eager to use them you need to look at

your queries first and only afterwards you can kind of understand what kind of indexes you might need and always tries to always try to Benchmark I try to Benchmark possibly not in production but with a large set of um of data entries and see is this query even really making use of the indexes I plan for it is this query uh does this really even need an

index perhaps it's just a report where you don't besides grouping you don't need to um and another thing is adding indexes has a cost but also the not having indexes might be more expensive if you use certain um database as a service such as Planet scale you pay based on the number of rows that the datab database reads right not you also have eess fees right but

the major cost is how many records is this database going to look at before providing the result set that I'm going to use for my own application and if you don't have any index and if you don't have like um proper structure for your data then you're going to probably have like millions and millions of record rows record scans every time you have a simple query and

those really um accumulate very fast you might going to end up paying uh more that you allotted for the budget of your project how do you avoid that you try to add indexes where it matters and you uh need to analyze them another thing is um every secondary index just remember they have a copy of the primary key so try to make the primary Keys as small

as possible but also uh not too small otherwise you're going to run out of room and you're going to have a painful time with migrations and secondly um if you use multicolumn index make sure that you access them in queries in a way that makes sense sense if you have like three columns and you only access the last two columns then you're not going to make use

of the index you created just through again think about prefixes you need to use the first n Columns of your multicolumn index and then it's going to kick in and database will make you uh happier and also sometimes you just need generated columns uh you don't need to store them on dis necessarily but you might work with unstructured data such as Json you might need to perform

on it and the only way you have currently to have indexes on it is to generate things such as we've seen with Jon columns um that's all for me if you want to learn more about indexes here there are a few resources I'm going to share the slides I'm not sure how but I have a link online that I can share uh with you all uh thanks

so much for having me here thank you for your presentation and this very valuable information on indexes and how to make your like the use of your database do we have questions in the whole year right now okay you can think about it and let's jump on to a couple of slider questions until we have time so first one generated column plus normal index versus function based

index what's your take on it uh function based indexes first of all they are I always forget their syntax but fun most cases they are equivalent like you use them for the same things for for the Json example you could use these function indexes uh if you use an RM or like other type of obstructions of your database most of them have no idea how to deal

with function indexes so you might have a a bit of a problem there uh in general I would say don't over complicated like I still have to encounter a situation where I really needed them uh but if you do please uh come and talk with me afterwards I'll be curious thank you next one would be how do you go about analyzing tradeoffs for deciding the amount uh

and size of indices any more rules or thumb other than list indices necessary um it really depends on how much do you know about your queries beforehand so if you know if you can plan ahead just take a look at your queries take a look at how what kind of columns they access for a specific table and if you have joints always remember to index the joints

and if you have maybe uh a multicolumn um I don't know um you have a many to many relation relation right right you might benefit from having perhaps a primary key that is a multicolumn uh where you have basically one column for every relation you're joining in there uh that's going to make things faster uh besides that this is like one case I didn't mention in the

slides but beside that there are no more rule of thumbs uh like it's just it you need to look at analyze you you need to look at uh your query plans um and that's about it okay then two Blitz questions one is what would happen if you index every column uh it will work uh but if you just use single indexes it's not going to be that

useful because the moment you filter by one uh you know remember that my SQL always chooses a single index per query uh so you're just going to have a lot of wasted dis disk space um yeah don't do that okay it's a waste what is the perfect Most Wanted selectivity uh the highest you can get like if you have a unique key that's the best one if

whenever you have a key that filters by a single ID like that's the best activity you can get all righty then we have still time for one more question which is what tools could you recommend to audit the queries which could be considered for indexing oh uh so I I didn't plan ah head for this but there is uh one tool we just released I think last

week for uh from Prisma called Prisma optimize it's going to take a look at all the queries you run via Prisma and it's going to basically order them by uh runtime inefficiency and from there we give you insights like this my benefit from an from an index in this query in this column or this other column or you might use this other kind of query to have

the same result uh I don't have a link right now because this was not a sponsor talk but if you are curious uh contact me afterwards all righty questions in the hole still none all right it then again thank you for your talk thank much and uh you can catch Alberto outside and ask him more questions thank you