SQL-Index-Assistent

SQL Index Recommendations from Query Analysis

Database indexes are the primary mechanism for query performance optimization — a well-chosen index can reduce query execution from minutes to milliseconds by allowing the database to locate rows without scanning entire tables. However, indexes are not free: they consume storage, slow down write operations, and can actually harm performance if poorly designed. The SQL Index Helper analyzes your queries to recommend optimal indexes based on column usage in WHERE, JOIN, ORDER BY, and GROUP BY clauses, generating ready-to-use CREATE INDEX statements for MySQL, PostgreSQL, and MariaDB.

Paste one or more SQL queries and receive index recommendations ranked by expected impact. The tool identifies which columns benefit from indexing, suggests composite indexes with optimal column ordering, detects existing index patterns that may already cover the query, and generates dialect-specific DDL statements you can execute directly against your database.

Index Recommendation Logic

The tool analyzes SQL clauses to determine which columns benefit from indexing:

• WHERE clause columns: Equality conditions (=) should be indexed first, followed by range conditions (<, >, BETWEEN)
• JOIN columns: Foreign key columns used in ON clauses benefit significantly from indexes on both sides of the join
• ORDER BY columns: Indexes matching the sort order avoid expensive filesort operations
• GROUP BY columns: Matching indexes enable streaming aggregation without temporary tables
• Covering indexes: When SELECT columns are all present in an index, the database can satisfy the query from the index alone without reading table rows

Composite Index Column Ordering

The order of columns in a composite index dramatically affects its utility. The tool follows best practices for ordering:

• Equality columns first: Columns compared with = go before range columns
• Higher selectivity first: Columns that filter more rows (more unique values) placed before less selective columns
• Range column last: Only one range condition can use the index per query — it must be the rightmost column
• Sort columns after filters: ORDER BY columns appended after WHERE columns enable index-ordered retrieval

A composite index on (status, created_at) serves WHERE status = 'active' AND created_at > '2024-01-01' optimally — the equality on status narrows the scan range, then created_at uses the remaining index for range filtering.

Dialect-Specific Index Generation

The tool generates CREATE INDEX statements appropriate for your database dialect:

• MySQL/MariaDB: CREATE INDEX idx_name ON table (col1, col2) with optional USING BTREE or USING HASH
• PostgreSQL: CREATE INDEX CONCURRENTLY for non-blocking index creation on production tables
• Partial indexes (PostgreSQL): CREATE INDEX ... WHERE status = 'active' for queries that always filter on a specific value
• Index naming convention: Follows idx_{table}_{columns} pattern for consistency

When generating indexes, the tool also considers storage implications — composite indexes with many columns or on large text fields can significantly increase disk usage and write amplification, so recommendations include size estimates when the column types suggest potentially large index entries.

Code Examples

Query to Index Recommendation

-- Input query:
SELECT id, name, email, created_at
FROM users
WHERE status = 'active'
  AND country = 'US'
  AND created_at > '2024-01-01'
ORDER BY created_at DESC
LIMIT 50;

-- Recommended index (MySQL):
CREATE INDEX idx_users_status_country_created
  ON users (status, country, created_at DESC);

-- Recommended index (PostgreSQL — with CONCURRENTLY):
CREATE INDEX CONCURRENTLY idx_users_status_country_created
  ON users (status, country, created_at DESC);

-- Explanation:
-- 1. status (equality) — narrows to ~20% of rows
-- 2. country (equality) — narrows further to ~5%
-- 3. created_at DESC (range + sort) — enables range scan in sort order
-- Result: Index-only scan returning sorted results without filesort