Adding indexes to custom tables for faster queriesfor Drupal 11 , 10 , 9 , and 8

Think of a phone book with no alphabetical order — finding one name means reading every single page. That's exactly what a database does when it queries a column with no index: a full table scan, checking every row one by one. An index is the alphabetical ordering that lets the database jump straight to the rows that match, and for a custom table you define yourself, adding the right indexes is entirely your responsibility — Drupal core won't do it for you.

What you'll learn in this lesson

• How to declare indexes in a module's hook_schema() implementation
• Which columns are actually worth indexing — and which aren't
• How to verify an index is actually being used, not just present

Declaring an index in hook_schema()

/**
 * Implements hook_schema().
 */
function performance_example_schema() {
  $schema['performance_example_log'] = [
    'description' => 'Stores custom performance log entries.',
    'fields' => [
      'id' => ['type' => 'serial', 'not null' => TRUE],
      'uid' => ['type' => 'int', 'unsigned' => TRUE, 'not null' => TRUE],
      'event_type' => ['type' => 'varchar', 'length' => 64, 'not null' => TRUE],
      'created' => ['type' => 'int', 'not null' => TRUE],
    ],
    'primary key' => ['id'],
    'indexes' => [
      'uid' => ['uid'],
      'event_type_created' => ['event_type', 'created'],
    ],
  ];
  return $schema;
}

The indexes key is where the real work happens. 'uid' => ['uid'] creates a single-column index — useful the moment you filter or join on uid alone. 'event_type_created' => ['event_type', 'created'] creates a composite index across two columns together, built specifically because a common query filters by event_type and then sorts by created — the composite index serves both parts of that query in one pass.

Which columns are actually worth indexing

Index every column you filter on with condition(), join on, or sort with orderBy() — but not every column in the table. A column with very few distinct values (a boolean status flag, for instance) usually gives the database little to narrow down and adds write overhead for little read benefit. A column like event_type with many distinct values, queried constantly, is exactly what indexes are for.

Verifying an index is actually used

Declaring an index doesn't guarantee the database uses it for a given query. Run EXPLAIN in front of the raw SQL (visible via Drupal's Devel module query log, or directly against the database) and check the key column in the output — if it names your index, the database used it; if it says NULL, the query is still doing a full table scan despite the index existing, usually because the query filters on a different column, or wraps the indexed column in a function that prevents index use.

Quick check: you add an index on event_type alone, but your real query filters on event_type AND sorts by created. Will the single-column index still help the sort? (Only partially — it speeds up finding rows matching event_type, but the database still has to sort those matches separately unless created is part of the same composite index, in the right column order.)

Key takeaways

• Declare indexes under the indexes key in hook_schema() — Drupal never adds them automatically for a custom table.
• Index columns you actually filter, join, or sort on — not every column, since indexes add write overhead.
• A composite index across two columns can serve a query that filters on the first and sorts on the second, in that column order.
• Use EXPLAIN to confirm the database is actually choosing your index, not just that the index exists.

Coming up next

Custom tables aren't the only thing you query constantly — entity queries against nodes, users, and other content run everywhere in Drupal. Next, we look at how entity query results get cached, and when that cache helps you versus quietly serves stale data.