PostgreSQL and MySQL Indexing Secrets: Speed Up Slow Queries on 10-Million Row Tables

When your database table grows past 10 million rows, bad queries can no longer hide. A query that took 12 milliseconds in staging can freeze your production server for 15 seconds. Disk input-output spikes, CPU usage hits 100 percent, and connection pools fill up immediately.

Most developers respond by throwing random indexes at the problem. That usually makes things worse by slowing down writes and eating gigabytes of memory. Here are battle-tested indexing secrets for PostgreSQL and MySQL that make massive tables lightning-fast without wasting system resources.

1. Master the Equality, Sort, Range (ESR) Rule

A B-Tree index works just like an old printed phone book. It organizes data in sorted order from left to right. If your multi-column index does not match how your query filters and sorts data, the database engine ignores half of your index and scans millions of rows anyway.

Follow the ESR rule when building composite indexes:

• Equality first: Put columns checked with exact values (WHERE status = 'paid') on the far left.
• Sort second: Put columns used in ORDER BY right after equality columns.
• Range last: Put columns filtered with inequalities (>, <, BETWEEN) at the very end.

Here is an example for an orders table:

-- Bad query plan: checks range before equality
-- CREATE INDEX idx_bad ON orders (created_at, status);

-- Good query plan following ESR:
-- Status is exact match, created_at is sorted or range filtered
CREATE INDEX idx_orders_status_created 
ON orders (status, created_at);

If you put the range column first, the database engine stops using the rest of the index as soon as it hits the range condition. Putting equality first keeps the search window as tight as possible.

2. PostgreSQL Secret: Cut Index Size with Partial Indexes

In a table with 10 million rows, you rarely care about every single row. For example, 95 percent of background jobs in your table might already be marked as completed. You only query the 5 percent that are pending or failed.

In PostgreSQL, you do not need to index all 10 million rows. You can build a partial index with a WHERE clause:

-- Creates an index ONLY for unprocessed queue jobs
-- Drops index size from 450 MB down to roughly 15 MB
CREATE INDEX idx_jobs_pending 
ON background_jobs (priority, created_at) 
WHERE status = 'pending';

Because the index only holds a fraction of the rows, it fits entirely into RAM cache. Your queries run in under 2 milliseconds, and insert operations on finished jobs stay super fast.

3. MySQL Secret: Save Memory with Prefix Indexing

MySQL does not support partial indexes like Postgres, but it has its own trick: prefix indexing on wide text columns. Indexing full UUIDs, URLs, or email strings takes massive amounts of buffer pool memory.

If your table contains long strings, index only the first few characters needed to ensure uniqueness:

-- Check how unique the first 10 characters are
SELECT 
  COUNT(DISTINCT LEFT(tracking_token, 10)) / COUNT(*) AS selectivity
FROM shipments;

-- If selectivity is over 0.99, index only the first 10 characters
CREATE INDEX idx_shipments_token 
ON shipments (tracking_token(10));

This trick cuts the index footprint in half. Smaller indexes mean MySQL keeps more data pages in the InnoDB buffer pool, preventing slow disk reads.

4. Stop Hiding Columns Inside Functions

A classic mistake that wrecks performance on 10-million row tables is wrapping indexed columns inside functions. When you wrap a column in a function, the database cannot use standard B-Tree lookups.

-- Slow: The engine must run DATE() on all 10,000,000 rows
SELECT id, amount 
FROM transactions 
WHERE DATE(created_at) = '2024-03-01';

-- Fast: The engine uses the index directly with a range search
SELECT id, amount 
FROM transactions 
WHERE created_at >= '2024-03-01 00:00:00' 
  AND created_at < '2024-03-02 00:00:00';

If you cannot rewrite the query, create an expression index in PostgreSQL:

-- Postgres functional index
CREATE INDEX idx_trx_date ON transactions (DATE(created_at));

5. Eliminate Table Lookups with Covering Indexes

When an index matches your filter, the database still has to jump over to the primary table heap to read unindexed columns. On huge tables, these secondary lookups create thousands of random disk read operations.

You can eliminate this extra work by using covering indexes. In PostgreSQL, use the INCLUDE clause:

-- Satisfies query directly inside the index tree (Index Only Scan)
CREATE INDEX idx_users_active_lookup 
ON users (company_id, email) 
INCLUDE (first_name, last_name);

In MySQL, simply append the extra columns to the end of your composite index. Because the database finds everything it needs directly inside the index leaf nodes, it never touches the main table data blocks.

6. Build Indexes Without Locking Production

Running a standard CREATE INDEX on a 10-million row table locks write operations. If writes block for minutes, your application queues back up and crashes your servers.

Always build indexes online using safe commands:

-- PostgreSQL: Builds the index in background without write locks
CREATE INDEX CONCURRENTLY idx_payments_user 
ON payments (user_id);

-- MySQL: Uses online DDL to allow reads and writes
ALTER TABLE payments 
ADD INDEX idx_payments_user (user_id), 
ALGORITHM=INPLACE, LOCK=NONE;

Common Pitfalls and Edge Cases

Keep these traps in mind before touching your production database:

• Low selectivity columns: Never index columns with only 2 or 3 distinct values (like a boolean is_active flag) on their own. The query planner will skip the index and choose a full table scan.
• Unused index waste: Every index slows down INSERT, UPDATE, and DELETE statements. Query your database statistics regularly to drop indexes with zero scans.
• Implicit type conversions: If your column is a VARCHAR, querying it with an unquoted integer will disable your index. Always match your query variable types to the column schema.

Summary Checklist

Before launching queries against 10-million row tables, run EXPLAIN ANALYZE in Postgres or EXPLAIN in MySQL. Confirm that the query cost is low, the plan shows an index seek rather than a sequential scan, and row estimates match reality. Order your columns using the ESR rule, trim index sizes with partial or prefix indexes, and always create indexes concurrently.