7.1 Reasons for the appearance of indices
Another important thing without which there can be no databases is indexes.
Imagine a situation where there are 10 million users in the user table , and you want to display everyone who has a level above 90. This query is very simple to write:
SELECT * FROM user WHERE level > 90Great, we wrote the request in less than a minute. And how long will it take to execute this query from the SQL server? To execute such a query, he will have to go through 10 million records, and even if there is only one record, it will take a lot of time.
How would we do a similar task in Java? We would first sort the collection of users by level, and then we could very quickly find the necessary records using a binary search. I hope I don't need to explain what it is?
Great, but what if we now need to select users whose registration date was before 2020? Sort again by registration date and use binary search.
Yeah, if we perform a filter on some field, and not just once, but often, then it will be very useful to store the data sorted by this field.
And how to store data sorted simultaneously by different fields?
And the answer is very simple - you need to store not the data itself, but their indexes in some global table.
Let's say there are 10 users with id: {1, 2, 3, 4, 5, 6, 7, 8, 9, 10}.
And you decide to sort them by level, then the array of their id will be, for example, like this: {9, 2, 3, 1, 5, 4, 8, 6, 7, 10}.
And if we sort them by date, then we get, for example: {10, 1, 8, 7, 2, 3, 5, 9, 6}.
Arrays of these id are called indexes . The elements themselves are large, we do not touch them. In Java, we do not touch objects, but store their references; in SQL, we do not touch real strings, but store their numbers.
Let me rewrite this in Java code:
List<String> list = List.of("A", "C", "B", "Z", "Cc", "Bb", "Zz", "Y"); //this is a list of objects
List<String> alphabeticsList = new ArrayList(list);
Collections.sort(alphabeticsList); //collection sorted alphabetically
List<String> lengthList = new ArrayList(list);
Collections.sort(lengthList, lengthComparator); //collection sorted by string lengthSorting collections does not mean moving the actual elements. The collection does not store real objects, but links to them. The same is true for SQL tables. Real lines lie to themselves and lie.
And when we need to frequently make selections for some field, then we add another index to the table (analogous to a new collection in Java) and sort the rows of the table, store their sorted order in a special index file.
I hope the Java comparison helped a little. A little practice - and for you, the use of indexes will also become the most obvious solution.
7.2 Adding indexes to a table
The index can be specified immediately during the creation of the table, or added after. Most often, it is the second scenario that occurs - indexes are added as the table size grows and data sampling slows down.
Adding an index to a table is very simple:
ALTER TABLE table
ADD INDEX index_name (column);If you often look up records in multiple columns at the same time, you can specify a composite index: SQL uses multiple columns to compose it.
Adding a composite index to a table is also very simple:
ALTER TABLE table
ADD INDEX index_name (column 1, column 2, column 3, ...);Indexes take up a lot of disk space, so if you no longer need an index, you can always remove it:
ALTER TABLE table
DROP INDEX index_name;The indexes themselves are a rather hidden part of the database. They do not affect the format of writing queries in any way. It's just that their presence speeds up data sampling and slows down their addition and backup.
But considering how important speed is in today's world and how cheap disk space is, feel free to add indexes for all occasions. Forgive me admins...
GO TO FULL VERSION