Sooner or later every Drupal web developer comes across the situation when the site “has gone down” and must be “got back to life”. The reasons for that may be diverse, but in this particular article we’ll elaborate on one certain cure that's capable of increasing Drupal productivity, i.e. INDICES.
What are indices?
Indices are applied for quick search of lines that contain one indicated column value. Without an index function the whole table is being read through from the top data record until the relevant lines will be found. The bigger the table, the bigger the expenses. In the case of the table containing an index for the required column, MySQL can identify the search position in the middle of the data file without leafing through the whole bulk of data. In the case of a table consisting of 1000 lines, the search will be at least 100 times faster as compared to successive examination of all notes. But if access to all 1000 lines is needed, the successive reading appears to be faster since the disc search operation is not required.
Let’s look at one simple example. Say, we have the following view:
which generated the following query:
SELECT users.uid AS uid, users.name AS users_name, users.created AS users_created FROM users ORDER BY users_created DESC
We have ntentionally elected to examine the simplified variant, so that the basics might be better understood.
This simple query having made, the database addresses to the required table, takes the data, sorts them out according to the query and delivers the result.
And this gets repeated each time the query is set.
Let’s create an index:
mysql> CREATE INDEX my_user_created ON users (users_created, users_name, users.uid));
The system will create some sort of “table copy” which will exhibit the data in the following order: "users_created, users_name, users.uid". Now, in response to the query, the system won’t have to sort out the data but will only show the content of the index.
In case of the following query:
SELECT users.uid AS uid, users.name AS users_name, users.created AS users_created FROM users WHERE uid BETWEEN 1 AND 12 ORDER BY users_created DESC
the following index must be created:
mysql> CREATE INDEX my_user_uid ON users (users.uid, users_created, users_name));
In this case the system will not have to look through the whole table once the query's been made, because the ordered data are already in the index. Hence, some part of the index table will be cut out and presented as the end result.