I’m guessing you’d have to search the database to make the index, right? To search for ‘gazter’ you’d have had to go over the whole dataset and assigned each entry with a starting letter value, and so on?
When it comes to searching the database, the index will have already been created. When you create an index, it might take a while as the database engine reads all the data and creates a structure to shadow it. Each engine is probably different and I don’t know if any work exactly like that, but it’s an intuitive way to understand the basics of how B-trees work. You don’t really need to think much about how it works, just that if you want to use a column as a filter, you want to index it.
However, when you’re thinking about the structure of a database it’s a good idea to think what you’ll want to do with it before hand and how you’ll structure queries. Sometimes searching columns without an index is unavoidable and then you’ve got to come up with other tricks to speed up your search. Like your doctor might find you (i’m presuming gaz is sort for gary and/or gareth here) with a query like
SELECT*FROM patients WHERE birthdate = "01-01-1980" AND firstname LIKE "gar%"
The db engine will first filter by birthdate which will massively reduce the amount of times it has to do the more intensive LIKE operation.
That’s very clear, thanks.
I’m guessing you’d have to search the database to make the index, right? To search for ‘gazter’ you’d have had to go over the whole dataset and assigned each entry with a starting letter value, and so on?
When it comes to searching the database, the index will have already been created. When you create an index, it might take a while as the database engine reads all the data and creates a structure to shadow it. Each engine is probably different and I don’t know if any work exactly like that, but it’s an intuitive way to understand the basics of how B-trees work. You don’t really need to think much about how it works, just that if you want to use a column as a filter, you want to index it.
However, when you’re thinking about the structure of a database it’s a good idea to think what you’ll want to do with it before hand and how you’ll structure queries. Sometimes searching columns without an index is unavoidable and then you’ve got to come up with other tricks to speed up your search. Like your doctor might find you (i’m presuming gaz is sort for gary and/or gareth here) with a query like
SELECT * FROM patients WHERE birthdate = "01-01-1980" AND firstname LIKE "gar%"The db engine will first filter by birthdate which will massively reduce the amount of times it has to do the more intensive LIKE operation.