Warning message

Documentation is currently being migrated into the new system. Some pages might be temporarily missing, and some guides might appear empty. Thank you for your patience while we are improving Drupal.org documentation.

Understanding the logic behind EXPLAIN ANALYSE

Last updated on
September 8, 2016 - 14:47

When processing queries, the database has two possibilities:

  • Access the data directly using indexes.
  • Perform sequential scan on disc or in memory.

Sequential scans on large tables are considered evil, because they eat database memory and CPU time. A sequential scan on 100.000 rows will consume 100.000 CPU cycles . Even more when scanning from disc.

Therefore, the logic behind debugging queries is to run EXPLAIN on each slow query and hunt for sequential scans.

When you find a sequential scan, try to rewrite the query.
Rewriting queries takes both time and effort.

Here are examples for the most simple cases:

  • Add an index on columns involded in sequential scans.
  • Example:
    SELECT foo FROM table WHERE bar=1
    Of course, bar should carry an index.

  • Make sure that JOIN left and right colums have indexes.
SELECT foo, bar FROM table1 t1
INNER JOIN table2  t2 on t1.a = t2.a
WHERE t2.c=1

Of course, t1.a and t2.a should carry indexes.

The guide covers detailed techniques for optimizing queries.

Usually, you can always transform a sequential query into a collection of INNER, LEFT and RIGHT JOINs which does not produce a sequential scan or produces it in the very last steps, when minimal information can be loaded in memory and computer fast.