How to Find Missing Indexes in SQL Dumps

A missing index is a column or set of columns used frequently in WHERE, JOIN, or ORDER BY clauses that lacks a corresponding database index. You identify candidates by scanning your SQL dump for these query patterns and comparing them against the existing CREATE INDEX statements in the schema.

Why Missing Indexes Slow Down Your Database

Databases store data in tables, which are logically unordered sets of rows, though physical storage often uses structures like clustered indexes or heaps. When you run a query like SELECT * FROM orders WHERE customer_id = 42, the database engine chooses an access path based on available indexes and statistics. If customer_id is indexed, it can perform an efficient index seek or range scan; without an index, it may resort to a full table scan, which is manageable for tiny tables but costly for large ones.

An index is a separate data structure, typically a B-tree, that stores copies of specific columns in sorted order. It allows the database to jump directly to the relevant rows rather than scanning the entire table. If you query by customer_id but only have an index on order_date, the engine ignores the index and scans the whole table again. The goal of finding missing indexes is to identify these mismatches between what you query and what you have indexed.

Identifying Indexing Bottlenecks from Raw SQL

You can identify candidate missing indexes from a SQL dump alone, but confirming they are actually bottlenecks requires checking live query plans, workload frequency, and data distribution. The clues are embedded in the SQL dump itself, specifically in the application code or stored procedures included in the dump, and in the schema definition. Look for three specific patterns in your SQL files:

1. Foreign Key Joins: Columns used in JOIN ... ON clauses are prime candidates. If orders.customer_id joins with customers.id, ensure orders.customer_id is indexed.
2. Filter Columns: Columns appearing in WHERE clauses with equality (=) or range (>, <) operators.
3. Sort Columns: Columns used in ORDER BY clauses. An index on these columns can allow the database to retrieve rows in the desired order without a separate sort step, provided the optimizer chooses to use the index and the index matches the query's ordering, filters, joins, and direction.

Compare these frequently used columns against the CREATE INDEX statements present in your dump. Any column appearing in the patterns above but missing from the index list is a potential bottleneck.

Step-by-Step: Analyzing Your Schema Locally

Manually cross-referencing hundreds of lines of SQL is error-prone. You can use SchemaSync to automate the detection of missing keys and bottlenecks directly from the raw SQL dump. It runs entirely offline in your browser, meaning your schema structure never leaves your device, which is critical if your dump contains sensitive table names or column structures.

Here is a realistic example of a PostgreSQL dump snippet and the analysis process.

Input: Raw SQL Dump Snippet

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    order_date TIMESTAMP NOT NULL,
    total_amount DECIMAL(10, 2),
    status VARCHAR(20) DEFAULT 'pending',
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

-- Existing Indexes
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_customers_email ON customers(email);

Common Queries in Application Code (not shown in dump but known):

SELECT * FROM orders WHERE customer_id = 105 AND order_date > '2023-01-01';
SELECT * FROM orders WHERE customer_id = 105 ORDER BY order_date DESC;

Analysis Logic:

1. The query filters on customer_id and order_date.
2. The query sorts by order_date.
3. Existing indexes are on status and email.
4. There is no index on customer_id or order_date in the orders table.
5. Result: The database likely performs a full scan of the orders table for every query, assuming the table size justifies it.

Interpreting Normalization and Indexing Suggestions

When an analyzer identifies a bottleneck, it suggests specific index types. For the example above, a single-column index on customer_id would help, but a composite index is superior because the queries filter on two columns and sort by one of them.

The optimal index is CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date);.

Why this order? In a composite index, the leftmost column is the primary sort key. Since you always filter by customer_id first, it should be first. The second column, order_date, helps with the range filter and the ORDER BY clause. If you reversed the order to (order_date, customer_id), the index would be less effective for queries that filter only by customer_id.

Normalization suggestions often accompany indexing advice. If you see redundant data, such as storing the customer_name in the orders table to avoid joins, the analyzer might suggest removing that column and relying on the foreign key join instead. This reduces write overhead and storage, provided the join is indexed.

Applying Optimizations Without Breaking Logic

Adding indexes is generally safe for read performance but can slow down write operations (INSERT, UPDATE, DELETE) because the database must update the index structure alongside the table data. To apply optimizations safely:

1. Start with the highest impact: Add the composite index identified in the previous section.
2. Test write performance: Insert 1,000 rows and measure the time. If it becomes significantly slower, consider if the index is truly necessary for all queries or if a partial index (indexing only status = 'pending') would suffice.
3. Avoid over-indexing: Do not create an index for every column in a WHERE clause. Focus on columns used in joins and high-selectivity filters (columns with many distinct values). Low-selectivity columns like status (which might only have 3-4 values) often benefit less from indexing unless combined with other columns.

Verifying Performance Improvements

You must verify that the new index actually improved performance. Do not rely on intuition. Use the EXPLAIN command in your database shell.

Before Index:

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 105 AND order_date > '2023-01-01';

Output:

Seq Scan on orders  (cost=0.00..1500.00 rows=100 width=50)
  Filter: ((customer_id = 105) AND (order_date > '2023-01-01'))
  Rows Removed by Filter: 9900
  Total time: 2000ms

After Index:

CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date);

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 105 AND order_date > '2023-01-01';

Output:

Index Scan using idx_orders_customer_date on orders  (cost=0.28..8.30 rows=100 width=50)
  Index Cond: ((customer_id = 105) AND (order_date > '2023-01-01'))
  Total time: 50ms

The key indicators are the change from Seq Scan to Index Scan and the reduction in Total time. If the time does not drop, the index may not be being used (check for implicit type conversions) or the table is too small to benefit from indexing.