Database Indices

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 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, users.name 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.