Speed Up Your SQL Queries with Indexing (MySQL/PostgreSQL Tips)

Creating Indexes

To add an index to a table column, use the CREATE INDEX statement. Here’s how to create an index on the email column of a users table:

CREATE INDEX idx_users_email ON users(email);

This improves the performance of queries like:

SELECT * FROM users WHERE email = 'someone@example.com';

How Indexes Improve Performance

Without an index, a query must scan every row (a “full table scan”) to locate matching entries. With an index, the database engine quickly finds matches using the indexed values. This makes read-heavy operations—like searching and filtering—much faster, especially on large datasets.

Understanding Index Types

Different index types are suited for different workloads:

• B-Tree Index: Default index for most queries. Supports range scans and sorting.
• Hash Index: Fast for exact matches. Available in MySQL (e.g., with MEMORY tables).
• GIN (Generalized Inverted Index): Ideal for PostgreSQL’s JSONB and full-text search.
• GiST (Generalized Search Tree): Flexible structure, often used for geometric data.

Using Composite Indexes

Composite indexes span multiple columns. They are helpful when your queries use conditions on more than one column together. Example:

CREATE INDEX idx_name_email ON users(last_name, email);

This index is effective for:

• SELECT * FROM users WHERE last_name = 'Smith';
• SELECT * FROM users WHERE last_name = 'Smith' AND email = 'smith@example.com';

But not for:

• SELECT * FROM users WHERE email = 'smith@example.com'; (unless a separate index on email exists)

Indexing Pitfalls: When Not to Use Indexes

Although indexes improve query speed, they come with trade-offs:

• Each index uses disk space
• Indexes slow down write operations (INSERT, UPDATE, DELETE)
• Too many indexes can cause overhead

Avoid indexing:

• Low-selectivity columns (e.g., boolean fields)
• Frequently updated columns
• Temporary or staging tables

Analyzing Query Performance

To determine if your indexes are effective, use the EXPLAIN command:

EXPLAIN SELECT * FROM users WHERE email = 'someone@example.com';

This reveals how the query is executed, which indexes are used, and if the database is performing a full table scan instead of leveraging your index.