Database optimization for online stores

TTFB (Time To First Byte) is half of a site's speed. A server responding in 800ms makes the frontend irrelevant, no matter how many WebP images you have. Database optimization cuts this server time, with correct indexes, efficient queries, cache, and tuning on MySQL/PostgreSQL.

Why TTFB matters

TTFB = time from HTTP request to first byte received. Decomposes into: DNS + TLS connection + request processing + DB query + server rendering. In an online store, 60-80% of TTFB is time spent in the database.

Targets: under 200ms (excellent), 200-600ms (acceptable), above 600ms (problem), above 1s (critical). A TTFB of 800ms means LCP cannot drop below 800ms, whatever you do on the frontend.

Composite indexes: the foundation

90% of slow queries are fixed with correct indexes. Classic mistake: index on a single column when filtering on multiple.

Example: product listing by category, active, sorted:

SELECT * FROM products
WHERE category_id = 42 AND status = 'active'
ORDER BY sort_order DESC LIMIT 20;

Wrong index (one per column) = DB picks one index, then filters the rest in memory:

CREATE INDEX idx_category ON products(category_id);
CREATE INDEX idx_status ON products(status);

Correct index: composite, in selectivity order:

CREATE INDEX idx_cat_status_sort
ON products(category_id, status, sort_order DESC);

Result: query becomes index-only scan, only touches the table for the 20 returned rows. On 100k products: from 350ms to 2ms.

How to verify indexes: EXPLAIN

Put EXPLAIN in front of any slow query:

EXPLAIN SELECT ... ;

Read key columns:

• type / access type: ALL = full table scan (bad). index = full index scan (better). range, ref, eq_ref = good. const = ideal.
• rows: estimated rows read. If it reads 100k to return 20 = missing index.
• Extra: Using filesort and Using temporary = on-disk sort (bad). Using index = index-only (ideal).
• key: the index actually chosen. If NULL, no index was used.

N+1 query: the most frequent performance killer

Classic pattern with ORMs (Eloquent, Django ORM, Prisma, Hibernate): load a list of products, then for each product load its category, in a loop.

# BAD: 101 queries
products = Product.objects.all()[:100]
for p in products:
    print(p.category.name)  # 1 query per product

# GOOD: 1 query with JOIN (eager loading)
products = Product.objects.select_related('category').all()[:100]
for p in products:
    print(p.category.name)  # 0 extra queries

In Eloquent (Laravel): Product::with('category')->take(100)->get(). In Prisma: prisma.product.findMany({ include: { category: true } }). Detect N+1 with query logging: if you see 100+ queries on a single page, you have it.

Connection pool: hidden overhead

Opening a DB connection takes 20-50ms (TCP handshake + auth). If the app opens a connection per request, 1000 req/s = 20-50s just in handshakes.

• PostgreSQL: PgBouncer in transaction pooling mode. Reuses connections across requests.
• MySQL: ProxySQL or native pool in framework (HikariCP on Java, mysql2 pool on Node).
• Settings: pool_size equal to (CPU cores × 2) + disk spindles. Too large = OOM, too small = queue.

Redis cache: 90%+ hit rate

The product catalog rarely changes but is read on every request. Perfect for cache. Priority order:

• Object cache (WordPress/WooCommerce): Redis Object Cache plugin. Eliminates 80% of queries, so a slow WooCommerce becomes instantly fast.
• Application query cache: "top products", "categories", "recommendations" query results cached with 5-60 min TTL.
• Full page cache: static pages (home, category, product) served directly from Redis/Nginx. Invalidate on product edit.
• Sessions: move sessions from DB/filesystem to Redis. Faster, distributed.

Avoid the trap: cache without invalidation strategy = stale data (wrong price, expired stock). Use key versioning or event-driven invalidation.

Partitioning for large tables

Tables over 1M rows benefit from partitioning. Classic for stores: orders partitioned by month:

-- PostgreSQL declarative partitioning
CREATE TABLE orders (
    id BIGSERIAL,
    created_at TIMESTAMPTZ NOT NULL,
    customer_id BIGINT,
    total DECIMAL(10,2)
) PARTITION BY RANGE (created_at);

CREATE TABLE orders_2026_07 PARTITION OF orders
    FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');

A query on the current month reads only the current month partition (30 days), not the full 5-year history. Indexes are per-partition: smaller, faster. Maintenance (VACUUM, ANALYZE) can run per-partition without locking the whole table.

MySQL / PostgreSQL tuning: high-impact settings

MySQL (my.cnf)

• innodb_buffer_pool_size = 50-70% of total RAM. Most important setting.
• innodb_buffer_pool_instances = 1 per GB of buffer pool.
• innodb_log_file_size = 256MB-1GB for stores with many writes.
• query_cache_type = OFF: removed in MySQL 8+, on 5.7 causes lock contention.
• max_connections = pool_size × app instance count + margin.

PostgreSQL (postgresql.conf)

• shared_buffers = 25% of RAM.
• effective_cache_size = 50-75% of RAM (estimate, doesn't allocate).
• work_mem = 4-16MB (sort/hash per query; larger = more memory per connection).
• maintenance_work_mem = 256MB-1GB (VACUUM, CREATE INDEX).
• random_page_cost = 1.1 on SSD (default 4.0 is for mechanical disk).
• autovacuum = on, aggressive autovacuum_naptime on tables with many writes.

Slow query log: the diagnostic tool

Enable slow query log and analyze weekly. MySQL:

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 0.1   # 100ms
log_queries_not_using_indexes = 1

PostgreSQL: log_min_duration_statement = 100. Analyze with pt-query-digest (MySQL) or pg_stat_statements (PostgreSQL). Top 10 slow queries = top 10 optimization opportunities.

Concrete case: slow WooCommerce checkout

Store with 8,000 products, checkout took 4-8 seconds. Causes found via slow log:

• N+1 query: on shipping calculation, one query per cart product (5-15 queries).
• Missing index on wp_woocommerce_order_items(order_id).
• Inactive object cache (WordPress used filesystem).

Interventions: eager loading on shipping, added index, enabled Redis Object Cache. Result: checkout 4-8s → 0.8-1.2s. Conversion +23% in 30 days.