Must know SQL database optimisation techniques

Keeping Your Database Lean and Effective

These SQL database optimisation techniques covers indexing strategies, schema design, query tuning, and system-level adjustments. While there are many tools and approaches, it’s best to apply them based on careful analysis.

Indexing Strategies

Use Appropriate Indexes on Frequently Queried Columns

If you often filter on a certain column (e.g., WHERE status = 'active'), ensure that column is indexed. Primary keys should generally have clustered indexes for fast lookups.

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

Composite Indexes

When queries frequently involve multiple columns in the WHERE clause, a composite index might reduce the need for multiple single-column indexes. The order of columns in a composite index should match their usage in queries.

-- MySQL / PostgreSQL
CREATE INDEX idx_customer_date ON orders(customer_id, order_date);

Covering Indexes

A covering index includes all the columns referenced in a query (SELECT, JOIN, WHERE), allowing the database to retrieve data directly from the index without consulting the table. This…