Missing Index Detection¶

Use index findings to investigate SQL reported by a test. Install the module for the test database, start with @EnableQueryInspector, and compare each finding with the existing indexes and native query plan. Use representative fixtures and the same database engine as the application before deciding whether an index change is useful. Choose a workflow.

MissingIndexDetector reports clause-specific findings for WHERE, JOIN, ORDER BY, and GROUP BY. These findings describe the columns and index metadata it matched; they do not prove that adding an index will improve production performance.

Core Mechanism¶

Missing Index detection cross-references two inputs:

1. SQL column extraction -- Parse the query to identify which columns are used in WHERE, JOIN, ORDER BY, and GROUP BY clauses
2. Index metadata -- Use the database provider (SHOW INDEX for MySQL or PostgreSQL catalogs) and check whether each referenced column has an index

The match uses SQL structure and schema metadata rather than a selected EXPLAIN plan. Its accuracy depends on parsing and metadata support. Other predicates, composite or expression indexes, and optimizer choices can change the access path; review the full query before applying a suggestion.

Query: SELECT * FROM orders WHERE status = ?

Step 1: Extract WHERE column  --> status
Step 2: SHOW INDEX FROM orders --> check if 'status' has an index
Step 3: No matching index found --> ERROR finding to review with the query plan

Findings by SQL clause¶

1. Missing WHERE Index (missing-where-index)¶

Detects columns used in WHERE conditions that have no index.

Evidence: The detector extracted the WHERE column and found no matching index in the available metadata. Check both the extraction and the database's full index definition.

Example¶

-- Query
SELECT * FROM orders WHERE status = 'pending';

-- SHOW INDEX FROM orders
+--------+----------+-----------+
| Table  | Key_name | Column    |
+--------+----------+-----------+
| orders | PRIMARY  | id        |
+--------+----------+-----------+
-- 'status' column: no index found

-- \di+ orders
-- or: SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'orders';
+------------+-------------------------------------------+
| indexname  | indexdef                                  |
+------------+-------------------------------------------+
| orders_pkey| CREATE UNIQUE INDEX orders_pkey ON orders (id) |
+------------+-------------------------------------------+
-- No index covering 'status'

Report Output¶

[ERROR] Missing index on WHERE column
  Query  : SELECT * FROM orders WHERE status = ?
  Table  : orders
  Column : status
  Detail : SHOW INDEX FROM orders -> status column has no index
  Fix    : ALTER TABLE orders ADD INDEX idx_status (status)

Fix¶

ALTER TABLE orders ADD INDEX idx_status (status);

CREATE INDEX idx_status ON orders (status);

-- If 'customer_id' already has an index, and you also filter on 'status':
ALTER TABLE orders ADD INDEX idx_customer_id_status (customer_id, status);

2. Missing JOIN Index (missing-join-index)¶

Detects columns used in JOIN ON conditions that have no index.

Evidence: The detector matched an ON-clause column without a supported index in the metadata. Check the join's direction, predicates, and actual plan before choosing an index.

Example¶

SELECT o.id, m.name
FROM orders o
JOIN members m ON o.member_id = m.id;

If orders.member_id has no index:

[ERROR] Missing index on JOIN column
  Query  : SELECT o.id, m.name FROM orders o JOIN members m ON o.member_id = m.id
  Table  : orders
  Column : member_id
  Detail : SHOW INDEX FROM orders -> member_id column has no index
  Fix    : ALTER TABLE orders ADD INDEX idx_member_id (member_id)

[ERROR] Missing index on JOIN column
  Query  : SELECT o.id, m.name FROM orders o JOIN members m ON o.member_id = m.id
  Table  : orders
  Column : member_id
  Detail : No index found on orders.member_id
  Fix    : CREATE INDEX idx_member_id ON orders (member_id)

Fix¶

ALTER TABLE orders ADD INDEX idx_member_id (member_id);

-- This is typically a foreign key -- consider adding a FK constraint too:
ALTER TABLE orders ADD CONSTRAINT fk_orders_member
  FOREIGN KEY (member_id) REFERENCES members(id);

CREATE INDEX idx_member_id ON orders (member_id);

-- Add foreign key if not already present:
ALTER TABLE orders ADD CONSTRAINT fk_orders_member
  FOREIGN KEY (member_id) REFERENCES members(id);

3. Missing ORDER BY Index (missing-order-by-index)¶

Detects columns used in ORDER BY that have no index.

Evidence: The column is in the ORDER BY clause (structural) and has no index (schema).

Example¶

SELECT * FROM products ORDER BY created_at DESC LIMIT 20;

[WARNING] Missing index on ORDER BY column
  Query  : SELECT * FROM products ORDER BY created_at DESC LIMIT ?
  Table  : products
  Column : created_at
  Detail : SHOW INDEX FROM products -> created_at column has no index
  Fix    : ALTER TABLE products ADD INDEX idx_created_at (created_at)

[WARNING] Missing index on ORDER BY column
  Query  : SELECT * FROM products ORDER BY created_at DESC LIMIT 20
  Table  : products
  Column : created_at
  Detail : No index found on products.created_at
  Fix    : CREATE INDEX idx_created_at ON products (created_at)

Fix¶

-- MySQL
ALTER TABLE products ADD INDEX idx_created_at (created_at);

-- PostgreSQL
CREATE INDEX idx_created_at ON products (created_at);

-- Query: SELECT * FROM products WHERE category = ? ORDER BY created_at DESC

-- MySQL
ALTER TABLE products ADD INDEX idx_category_created_at (category, created_at);

-- PostgreSQL
CREATE INDEX idx_category_created_at ON products (category, created_at);

4. Missing GROUP BY Index (missing-group-by-index)¶

Detects columns used in GROUP BY that have no matching supported index in the metadata.

Evidence: The column is in the GROUP BY clause (structural) and has no index (schema).

Example¶

SELECT status, COUNT(*) FROM orders GROUP BY status;

[WARNING] Missing index on GROUP BY column
  Query  : SELECT status, COUNT(*) FROM orders GROUP BY status
  Table  : orders
  Column : status
  Detail : SHOW INDEX FROM orders -> status column has no index
  Fix    : ALTER TABLE orders ADD INDEX idx_status (status)

[WARNING] Missing index on GROUP BY column
  Query  : SELECT status, COUNT(*) FROM orders GROUP BY status
  Table  : orders
  Column : status
  Detail : No index found on orders.status
  Fix    : CREATE INDEX idx_status ON orders (status)

Fix¶

-- MySQL
ALTER TABLE orders ADD INDEX idx_status (status);

-- PostgreSQL
CREATE INDEX idx_status ON orders (status);

-- Query: SELECT region, COUNT(*) FROM orders WHERE status = 'active' GROUP BY region

-- MySQL
ALTER TABLE orders ADD INDEX idx_status_region (status, region);

-- PostgreSQL
CREATE INDEX idx_status_region ON orders (status, region);

Sub-Type Summary¶

Composite Index Leading Column (composite-index-leading)¶

While not part of the core 4 sub-types, the CompositeIndexDetector works closely with MissingIndexDetector and deserves mention here.

Detects queries that use a non-leading column of a composite index in the WHERE clause while skipping the leading column. In MySQL's B-Tree indexes, this means the index cannot be used.

How B-Tree Composite Indexes Work¶

A composite index (a, b, c) is organized as a B-Tree sorted first by a, then by b within each a, then by c within each (a, b):

            Composite Index: idx_abc (a, b, c)

            B-Tree sorted by: a -> b -> c

    +-----------------------------------------------------+
    |  a=1, b=1, c=1                                      |
    |  a=1, b=1, c=2                                      |
    |  a=1, b=2, c=1    <-- b=2 values clustered          |
    |  a=1, b=2, c=3        under a=1                     |
    |  a=2, b=1, c=1                                      |
    |  a=2, b=3, c=2                                      |
    |  a=3, b=1, c=1                                      |
    +-----------------------------------------------------+

    WHERE a = 1 AND b = 2       --> Can use index (leftmost prefix)
    WHERE a = 1                 --> Can use index (leftmost prefix)
    WHERE b = 2                 --> CANNOT use index (skips 'a')
    WHERE b = 2 AND c = 1      --> CANNOT use index (skips 'a')
    WHERE a = 1 AND c = 1      --> Partial use (a only, c skipped)

Example¶

Assume a composite index idx_status_date (status, created_at) exists on orders:

-- This query skips the leading column 'status'
SELECT * FROM orders WHERE created_at > '2024-01-01';

[WARNING] Composite index leading column not used
  Query  : SELECT * FROM orders WHERE created_at > ?
  Table  : orders
  Column : created_at
  Detail : Index idx_status_date(status, created_at) exists but leading column
           status is not in WHERE clause
  Fix    : Include leading column in WHERE or create a separate index on created_at

Fix Options¶

-- If you can add the leading column to the query
SELECT * FROM orders
WHERE status = 'active' AND created_at > '2024-01-01';

-- If the query legitimately does not filter by status

-- MySQL
ALTER TABLE orders ADD INDEX idx_created_at (created_at);

-- PostgreSQL
CREATE INDEX idx_created_at ON orders (created_at);

False Positive Protections¶

The MissingIndexDetector includes several intelligent filters to reduce false positives. Understanding these helps you trust the results and avoid unnecessary index creation.

1. Soft-Delete Column Detection¶

Columns matching soft-delete patterns (deleted_at, is_deleted, deleted, removed_at, etc.) receive special treatment:

-- Downgraded to INFO (sole WHERE condition):
SELECT * FROM users WHERE deleted_at IS NULL;

-- Suppressed entirely (other filter exists):
SELECT * FROM users WHERE email = 'test@example.com' AND deleted_at IS NULL;

Recognized soft-delete column names: deleted_at, deleted, is_deleted, removed_at, deactivated_at, discarded_at, unsuspended_at

2. Low Cardinality Column Detection¶

Columns that likely have few distinct values receive special treatment:

Detection methods:

• Name-based: Exact matches (type, status, role, gender, category, level, kind, state, enabled, active, visible, locked, verified, approved, published, archived)
• Prefix-based: Columns starting with is_, has_, or flag
• Suffix-based: Columns ending with _type or _status
• Cardinality-based: Index metadata shows cardinality <= 10

3. Unique/Primary Key Short-Circuit¶

QueryAudit skips other suggestions when it recognizes an equality predicate on a column reported as part of a unique or primary key index on the same table:

• All other missing WHERE index warnings for that table are skipped
• Missing ORDER BY index warnings are skipped

This shortcut does not prove a one-row bound for every query shape. Review the complete key definition and predicate, especially for composite unique indexes or alternative filter branches.

-- No missing index warning for 'status' because 'id' is the primary key:
SELECT * FROM orders WHERE id = 123 AND status = 'pending';

4. Leading-Wildcard LIKE Exclusion¶

MissingIndexDetector skips only columns compared against a leading-wildcard literal (LIKE '%foo'); those are handled by LikeWildcardDetector and a B-tree wouldn't help anyway. Prefix LIKEs (LIKE 'foo%') and parameterized LIKE ? fall through to the regular missing-index check because the column can still benefit from a B-tree index.

5. IS NULL / IS NOT NULL Exclusion¶

NULL checks on non-soft-delete columns are skipped because they have poor selectivity and rarely benefit from B-tree indexes.

6. Composite Index Member Exclusion¶

If a column appears in any composite index for the table, MissingIndexDetector skips it and defers to CompositeIndexDetector to report whether the composite index is being used correctly.

7. GROUP BY with PK or Indexed WHERE¶

GROUP BY columns are skipped when:

• All primary key columns of the table are present in GROUP BY (functional dependency)
• A WHERE column on the same table already has an index (narrow result set makes GROUP BY indexing less impactful)

False Positive Protection Summary¶

The SHOW INDEX Cross-Verification Flow¶

The complete detection flow for every query:

                        +----------------------+
                        |  Intercepted Query   |
                        +---------+------------+
                                  |
                        +---------v------------+
                        |  Is it a SELECT?     |
                        +---------+------------+
                             Yes  |
                        +---------v------------+
                        |  Parse SQL:          |
                        |  - WHERE columns     |
                        |  - JOIN ON columns   |
                        |  - ORDER BY columns  |
                        |  - GROUP BY columns  |
                        +---------+------------+
                                  |
                        +---------v------------+
                        |  Resolve aliases     |
                        |  (e.g., o -> orders) |
                        +---------+------------+
                                  |
                  +---------------+---------------+
                  v               v               v
         +---------------+ +------------+ +---------------+
         |  For each     | | For each   | | For each      |
         |  WHERE col:   | | JOIN col:  | | ORDER BY /    |
         |               | |            | | GROUP BY col: |
         |  Apply false  | | SHOW INDEX | | Apply false   |
         |  positive     | | has index? | | positive      |
         |  filters,     | |            | | filters,      |
         |  SHOW INDEX   | |            | | SHOW INDEX    |
         |  has index?   | |            | | has index?    |
         +-------+-------+ +-----+------+ +-------+------+
              No |            No |             No |
                 v               v               v
           ERROR issue     ERROR issue     WARNING issue
           (or downgrade)                  (or downgrade)

How to Create Suggested Indexes¶

Single Column Index¶

ALTER TABLE orders ADD INDEX idx_status (status);

CREATE INDEX idx_status ON orders (status);

Composite Index (WHERE + WHERE)¶

When QueryAudit detects multiple unindexed WHERE columns on the same table:

ALTER TABLE orders ADD INDEX idx_customer_status (customer_id, status);

CREATE INDEX idx_customer_status ON orders (customer_id, status);

Composite Index (WHERE + ORDER BY)¶

For queries that filter and sort:

-- Query: SELECT * FROM products WHERE category = ? ORDER BY price ASC
ALTER TABLE products ADD INDEX idx_category_price (category, price);

CREATE INDEX idx_category_price ON products (category, price);

Composite Index (WHERE + GROUP BY)¶

For queries that filter and group:

-- Query: SELECT region, COUNT(*) FROM orders WHERE status = ? GROUP BY region
ALTER TABLE orders ADD INDEX idx_status_region (status, region);

CREATE INDEX idx_status_region ON orders (status, region);

Covering Index¶

For queries that only need specific columns, a covering index avoids table lookups entirely:

-- Query: SELECT name, email FROM users WHERE status = 'active'
ALTER TABLE users ADD INDEX idx_status_covering (status, name, email);

-- PostgreSQL supports INCLUDE for non-key covering columns:
CREATE INDEX idx_status_covering ON users (status) INCLUDE (name, email);

Partial Index (PostgreSQL only)¶

For soft-delete patterns where most rows match the condition:

-- Only index non-deleted rows (PostgreSQL)
CREATE INDEX idx_users_active ON users (email) WHERE deleted_at IS NULL;

ALTER TABLE users ADD is_active TINYINT(1)
  GENERATED ALWAYS AS (CASE WHEN deleted_at IS NULL THEN 1 ELSE NULL END) STORED;
ALTER TABLE users ADD INDEX idx_active_email (is_active, email);

Index Creation Best Practices¶

Related Rules¶

• composite-index-leading -- Composite index leading column not used
• dml-without-index -- Missing index on UPDATE/DELETE WHERE columns
• for-update-no-index -- FOR UPDATE without index (locking risk)
• covering-index-opportunity -- Covering index suggestion
• write-amplification -- Too many indexes cause write amplification