MySQL and PostgreSQL Indexes — How They Work and When to Create Them for Real Performance

Your database is slow. A query that should take milliseconds keeps you waiting seconds, sometimes minutes. The client complains the management software is sluggish. Warehouse doesn't respond. And you, developer or sysadmin, know the issue is often about indexes — or their absence.

At Meteora Web, we've seen dozens of projects stall for this. A wrong index is as harmful as a missing one. And Italian SMEs, with tight budgets, can't afford servers compensating for poorly written queries. We've worked with databases for years — MySQL and PostgreSQL are our workhorses alongside Laravel and WooCommerce — and we know that a good index is the difference between a site that sells and one that loses customers.

In this guide, we explain how indexes work, when to create them and when to avoid them, with practical examples you can test right away. No abstract theory: just what you need to run your database efficiently.

How do indexes work in MySQL and PostgreSQL?

An index is like the index at the back of a book: instead of flipping through all pages to find a word, you go directly to the right page. Technically, it's a separate data structure that stores an ordered copy of one or more columns, with a pointer to the original row. The most common structures are B-tree (balanced, used by both DBMS) and Hash (equality only, supported by PostgreSQL).

When you run a query like SELECT * FROM orders WHERE customer_id = 42 with no index on customer_id, the database performs a sequential scan — it examines every row in the table. With an index, it jumps directly to the matching records.

B-tree in detail

The B-tree keeps data sorted and balances the tree so each search takes a logarithmic number of accesses. It's the default type in both MySQL and PostgreSQL. It supports:

• Equality search: WHERE column = value
• Range search: WHERE column BETWEEN a AND b
• Sorting: ORDER BY column
• Prefix pattern: WHERE column LIKE 'abc%'

MySQL uses B-tree also for UNIQUE indexes and indexes on TEXT/VARCHAR with a prefix. PostgreSQL additionally offers GiST (for geometric or full-text data), GIN (for arrays, JSONB), BRIN (for spatially correlated data), and Hash (equality only).

Practical example: creating a simple index

-- MySQL and PostgreSQL
CREATE INDEX idx_orders_customer ON orders (customer_id);

-- To verify it's used (explained later)
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

Creating an index is fast, but it has a cost: it slows down INSERT, UPDATE, and DELETE because every modification to the table must update the index as well. Also, it takes disk space. That's why you don't index every column randomly.

When to create an index and when to avoid it?

The golden rule: create an index only if it solves a real query that your system runs regularly. Don't guess. We always start from slow query logs or EXPLAIN.

Signs that you need an index

• Queries performing sequential scans on large tables (thousands of rows or more).
• JOIN operations without indexes on key columns (foreign key indexes are often forgotten).
• Frequent ORDER BY on non-indexed columns.
• Filters on columns with high selectivity (few rows per value).
• Search queries on dates, categories, statuses (e.g., WHERE status = 'active').

When not to create an index

• Columns with very low selectivity (e.g., gender with only 'M' and 'F' on a huge table — the index won't help because it has to read half the rows anyway).
• Columns almost never used in WHERE, JOIN, or ORDER BY.
• Small tables (e.g., under 1000 rows): the overhead of an index can be worse than a scan.
• Columns with many updates (e.g., last_access changing every minute) — the index updates every time, slowing down writes.

Composite indexes: the secret weapon

If a query filters on multiple columns, a composite index can be much more efficient than two single-column indexes. The column order is crucial: put the one with highest selectivity first.

-- Useful for queries like: WHERE customer_id = 42 AND date > '2026-01-01'
CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date DESC);

Remember: the index can be used only for the leftmost prefix. With INDEX (a,b,c), it works for queries on a, a+b, a+b+c, but not for queries on b or c alone.

How to measure if an index is working?

Don't trust intuition. Use EXPLAIN to see if the database uses the index. A concrete example from our work:

EXPLAIN SELECT * FROM orders WHERE customer_id = 42 AND order_date > '2026-03-01';
-- Typical output: "Index Scan" or "Index Seek" = index is being used
-- "Seq Scan" or "Full Table Scan" = not using it

In PostgreSQL, the output includes scan type (Index Scan, Bitmap Heap Scan, Seq Scan), estimated rows and cost. In MySQL, EXPLAIN shows the type column: ref or range are good, ALL means full scan.

Practical tools

• Slow Query Log: enable it (in MySQL SET GLOBAL slow_query_log = 1) and analyze queries that take more than a second.
• pg_stat_user_tables (PostgreSQL): shows sequential scans vs index scans. High seq_scan on a large table indicates missing index.
• Performance Schema in MySQL: provides detailed metrics.

We always compare query cost with and without an index. Often an index reduces response time by 90% or more. But beware: if the index is never used, it's just wasted space and write slowness.

What is the difference between MySQL and PostgreSQL indexes?

Both support B-tree, but there are important differences.

Index types

• MySQL (InnoDB): B-tree, FULLTEXT (for text), SPATIAL (for geometry). No Hash index except for MEMORY tables. InnoDB clusters data on the primary key.
• PostgreSQL: B-tree, Hash, GiST, GIN, BRIN, SP-GiST. Much more variety. For example, use GIN for JSONB columns, GiST for geometric data.

Partial indexes (PostgreSQL only)

PostgreSQL allows creating partial indexes that index only a subset of rows. Example:

CREATE INDEX idx_orders_active ON orders (customer_id) WHERE status = 'active';

This index is smaller and faster for queries filtering active orders. MySQL does not have this feature natively (can be simulated with generated columns or materialized views, but not straightforward).

Functional indexes (PostgreSQL)

You can index the result of a function, e.g.:

CREATE INDEX idx_orders_year ON orders (EXTRACT(YEAR FROM order_date));

In MySQL, to achieve the same effect you need a generated virtual column and index it. More cumbersome.

Index clustering

In InnoDB (MySQL), the primary key is clustered: data is physically ordered by the PK. Secondary indexes point to the PK, not directly to the row. In PostgreSQL, indexes are always non-clustered, but you can use CLUSTER to reorder the table according to an index (one-time operation, not maintained automatically).

These differences matter when you design the schema. At Meteora Web, we choose PostgreSQL for complex projects requiring functional or partial indexes, while for e-commerce on WooCommerce (MySQL/InnoDB) we leverage PK clustering and carefully design secondary indexes.

How to avoid common index mistakes?

We've seen the same errors dozens of times. Here are the three worst.

Mistake 1: indexing columns used only in output

The index is useful only for WHERE, JOIN, ORDER BY, and GROUP BY. If a column appears only in SELECT, it's useless — unless you're doing a covering index (all columns in the query are in the index, so the database reads only the index without touching the table). Example: CREATE INDEX idx_orders_covering ON orders (customer_id, order_date) INCLUDE (total, status); (PostgreSQL supports INCLUDE, MySQL does not).

Mistake 2: too many indexes on a table

Each additional index slows writes. In a table with many INSERT/UPDATE (logs, real-time orders), having 10 indexes can cripple write performance. We recommend no more than 5-7 indexes per table, unless specific needs require more.

Mistake 3: ignoring column order in composite indexes

If the index is on (category, date) but the query filters only by date, the index won't be used. Design indexes based on real queries, not intuition.

An example from our work: a client e-commerce had a 200,000-row orders table. It had an index on (order_date, customer_id). Queries filtering by customer were very slow. We reversed the order, creating (customer_id, order_date). Response time dropped from 3 seconds to 30 milliseconds. The logic: users almost always filter by customer first, then by date. Column order must reflect selectivity.

What to do now

1. Identify slow queries — enable slow query log or use Performance Schema / pg_stat_user_tables.
2. Analyze with EXPLAIN — look for sequential scans on large tables.
3. Create a targeted index — choose columns used in WHERE, JOIN, ORDER BY. If multiple columns needed, create a composite index with correct order.
4. Verify the effect — repeat EXPLAIN and measure times. If the index is not used, remove it.
5. Monitor writes — if you notice INSERT/UPDATE slowdowns, you may have too many indexes. Review the strategy.

At Meteora Web, we leverage our experience to help Italian SMEs optimize databases. For further reading, check our full guide on SQL and Relational Databases or contact us for a personalized analysis.