Database Indices

Database Indices

Sooner or later every 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 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 an access to all 1000 lines is needed, the successive reading appeares to be faster since the disc search operation is not required (source).

Let’s look at one simple example. Say, we have the following view: 

which generated the following query:

SELECT users.uid AS uid, AS users_name, users.created AS users_created FROM users 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 a “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, AS users_name, users.created AS users_created FROM users 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.

5 votes, Rating: 5

Read also


Probably everybody have come across with ready-to-use site packages on Drupal where after installation you get a ready site with the set of necessary modules, personal theme and so on. There are...


Cron.php script is used in Drupal for content indexation, sending e-mails to subscribers, gathering rss-feeds and so on – modules can give task themselves to be done according to schedule.


While developing a real estate site for one of our clients we tried to import...


It’s a very helpful module, which allows inserting FileField right into the text editor. For example, in the process of writing this text an Insert button was pushed and the following image...