Indices: first step on & nb p; paths to & nb p; speed
Databases are specialized repositories of information, and working with them, we operate with concepts of data sets and operations on these data, forgetting that there is real equipment behind them. Creating a SQL query, we mean that all actions on the tables are carried out simultaneously, because the concept of time in the commands of the SQL language is absent. When our database is small, we may not notice that the database server needs time to process the table we specified, to search for the required row, to retrieve the necessary records. But with the growth of information stored in the database, this problem becomes more and more noticeable, and at a certain point it becomes clear that special measures need to be taken. We are starting to increase the performance of our server, setting up more and more fast equipment or trying to configure an existing one, in order to squeeze the maximum speed out of it, with the sole purpose to speed up the execution of queries to our database.
But before considering the issue at the “physical” level, it is necessary to find out whether it is possible to use some features that are provided by the database server itself. Very often, the reason for slow query execution is that the tables are not indexed. As a rule, if the tables do not have indexes, in most cases it is unlikely to significantly improve the performance of the database in other ways.
Using indexes, the database server will extract data much faster. Consider how a table index can speed up query processing. In a table that does not have an index, records are stored in a chaotic manner, and when trying to extract information, the database server will scan all records in order to establish a match with the conditions. As an example, take the MySQL server database, which stores information about products. Suppose that we need to get all the lines about the goods produced in a particular country, and if this operation is performed frequently, instead of iterating over all the lines, we can index the table with the items item across the field containing the country_id country numbers. The created index will contain an entry for each row in the table, and its entries will be sorted by the country_id field. Now, when executing queries, the database server can find out which records are needed from the index, and if we need country number 5, then, having reached index 6, the database server can stop searching – there are clearly more rows number 5 in the index will not be.
Similarly, in each database server there are algorithms for quickly finding rows in the middle of the index list, which allows you to optimize the search for the required rows. The given example does not explain the advantages of creating indexes, for example, before sorting a table by this field, and saving it in this form. Yes, such a solution could be applied if all queries were carried out with a search on this field, but we may need to sort on other fields, so we can create several indexes for the table for the most popular queries — for example, for the quantity of goods price and other parameters.
When selecting information from several tables, the indexes are even more efficient. The example described above reduced the browsing time in one table, but using indexes will give even greater speed when processing queries, in which information is extracted from several tables and combined. In these cases, a search is first performed on a row in one table, based on the information received, a row is searched for in the following table, and so on. Therefore, for fields that are involved in WHERE conditions, it is highly recommended to use indexes. It is clear that when joining tables, the fields most often used are those that ensure the uniqueness of rows in tables, so when creating tables, you have to worry about creating indexes for these fields. It is quite simple to do this – it is enough to specify when declaring the field of the key field PRIMARY KEY or UNIQUE, and for such fields an index will be created automatically. In our database in each table there is a field declared with such a key that allows you to immediately solve the problem of indexation for the most resource-intensive queries.
When using indexes, one should be aware of their shortcomings. Besides optimizing queries with WHERE conditions, indexes can be useful in other cases as well. Obviously, the selected data will also be sorted faster when indexed by this field. Also, data will be sampled faster when using the MIN () and MAX () aggregate functions to get the maximum and minimum values when grouping rows. In some cases, if it is necessary to extract information that the index itself can provide, calls to the database table are not made at all. There may be a wrong idea that it is necessary to index every field in the table – it will not be worse.