Database design and query optimization

Database design and query optimization is finding the slow queries that make an application slow, fixing them with evidence, and designing schemas that hold at the next stage of growth. I work with MySQL and PostgreSQL, with Redis caching where it helps: slow-query analysis with EXPLAIN plans, indexes that match your real access patterns, safe changes to large live tables, read replicas and connection pooling, and backups proven by restoring them. It is for applications that slow down as tables grow or when traffic peaks, and teams planning a schema change on a table they can't take offline.

The review comes first and is fixed-price: I read the slow-query logs, the EXPLAIN plans of the worst offenders and the application's own traces, and rank fixes by how much time they save across real traffic, not by how bad a single query looks. Larger changes, a schema redesign, replicas or a migration, are quoted separately after the review.

MySQL's slow query log records statements that take longer than a threshold you set, which makes it the natural starting list. EXPLAIN then shows how the database plans to run each one: which indexes it uses, how many rows it expects to read, and where it sorts or scans. Most fixes are unglamorous: an index that matches how the query filters and sorts, a query that loads related rows once instead of once per row, a pagination that doesn't count the whole table, a report moved to a replica.

Schema changes on large live tables are done online, in steps: add the new column or index without locking writes, backfill in batches, switch reads and writes over, and keep a way back at each step. Connection pooling, with PgBouncer for PostgreSQL, keeps a burst of application instances from exhausting the database's connections.

On the TopGear India rebuild, the slow pages came from slow SQL; rewriting the queries and indexing the tables for how the site actually reads them was part of how the main pages went from 1–3 min → 3 s.

It is not data warehousing or BI modelling, and it is not administering a vendor's managed ERP database. It is also not "add more hardware": bigger instances are sometimes right, but only after the review shows the time isn't going into a few fixable queries.