What is the reason to define indexes in hook_schema?

Question

How do I choose what fields to index and which ones not to? Is the index useful for when I set a condition in the database query for a quick look up of the rows? I guess this is an SQL question but I really want to know what indexes are used for Drupal entities.

What are all these indexes in the node table for?

  'indexes' => array(
    'node_changed'        => array('changed'),
    'node_created'        => array('created'),
    'node_moderate'       => array('moderate'),
    'node_frontpage'      => array('promote', 'status', 'sticky', 'created'),
    'node_status_type'    => array('status', 'type', 'nid'),
    'node_title_type'     => array('title', array('type', 4)),
    'node_type'           => array(array('type', 4)),
    'uid'                 => array('uid'),
    'tnid'                => array('tnid'),
    'translate'           => array('translate'),
  ),

Answer 1

MySQL Indexes

You can start by reading the MySQL reference.

You are right in saying that index is useful when you use a condition in your query. Deciding which fields to index is not as easy as it looks, though. There are pros and cons to each index, and it is not right to say that having an index always boosts overall performance at the expense of disk space.

Using index results in faster lookup of data when queries use the indexed columns in its conditions; however, large indexes can slow things down in cases of heavy inserts or updates to the table as any update to the rows result in updates to the index as well.

Therefore, you have to carefully decide if it is worth adding an index to the column even if you are going to use it in a query. In most cases it is.

On the next level, when you are using multi-column indexes, you have to be even more careful about the queries. Suppose you define:

CREATE TABLE test (
    id         INT NOT NULL,
    last_name  CHAR(30) NOT NULL,
    first_name CHAR(30) NOT NULL,
    PRIMARY KEY (id),
    INDEX name (last_name,first_name)
);

You might expect a query like this to use an index, but it won't.

SELECT * FROM test WHERE first_name = 'hw';

The reason is explained in the documentation:

The name index is an index over the last_name and first_name columns. The index can be used for lookups in queries that specify values in a known range for combinations of last_name and first_name values. It can also be used for queries that specify just a last_name value because that column is a leftmost prefix of the index (as described later in this section).

Drupal

As far as Drupal is concerned, it uses the Schema API to define those indexes. As you would see, these are common fields that are used in queries on the node table.