2026-07-27 · 11 min read · Performance

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.

TTFB above 600ms on your store? See our optimization service or request a free audit.

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 — 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.

Keep reading