Database Query Optimization for Shop Performance
A shop often feels slow long before the first pixel appears -- and the cause frequently lies in the database. Before the server can deliver any HTML, it has to query products, prices, categories and stock levels. If that query stalls, the whole page stalls. This waiting time lands directly in the Time to First Byte (TTFB), and web.dev names a value of 0.8 seconds (web.dev) or less as good, while anything over 1.8 seconds (web.dev) counts as poor. TTFB is no abstract lab figure: Amazon found that every additional 100 milliseconds (Amazon) of latency cost roughly 1 percent (Amazon) in sales. This article shows how to surface slow queries with EXPLAIN, resolve the infamous N+1 problem, avoid full table scans with indexes, and absorb recurring load with query caching -- distinct from pure page caching, which serves a different layer. Whoever cuts database time cuts TTFB at the root, instead of merely masking it.
Why the Database Decides TTFB
Time to First Byte measures the time from the request to the first received byte of the response. In server-rendered shops, this span contains the entire server processing -- and a considerable part of it is database time. A category page quickly triggers dozens of queries: product list, variants, prices per customer group, stock levels, reviews. Each one costs time, and they add up because they usually run sequentially. As long as these queries are running, the browser waits for the first byte -- and the user sees a blank page. How little patience remains is shown by a Google measurement: just 400 milliseconds (Google) of slower search result time reduced the number of searches by 0.6 percent (Google).
TTFB is not itself a Core Web Vitals metric, but it precedes every visible render. It pushes back First Contentful Paint and Largest Contentful Paint, because the markup only flows once the database work is done. A sound server optimization therefore looks not only at the web server and network, but at the queries themselves. The fastest web server is of little use if it waits for a sluggish query that, with the right index, could answer in milliseconds rather than hundredths of a second.
TTFB, Slow Query and EXPLAIN -- the Terms
Surfacing Slow Queries with the Slow Query Log
Optimization without measurement is guesswork. The first step is therefore to find the slow queries at all. Databases like MariaDB and MySQL offer a slow query log for this: it records every query that exceeds an adjustable time threshold. The vague feeling that the shop is slow thus turns into a concrete list of queries with runtime, frequency and affected tables. Whoever optimizes without this log is flying blind -- and may invest time in queries that rarely run.
-- Enable the slow query log and set the threshold to 0.2 seconds
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.2;
-- Also log queries that use no index (especially revealing)
SET GLOBAL log_queries_not_using_indexes = 'ON';
-- Then: evaluate the log periodically and prioritize
-- by total time (runtime x frequency)When evaluating, it is not only the individual runtime that counts, but the total time of runtime times frequency. A query that takes just 30 milliseconds on its own but runs twenty times per page view costs more than a rare 200-millisecond query. This very pattern -- many small, repeated queries -- is the transition to the N+1 problem, the most common and most insidious performance killer in shops. A systematic performance analysis connects the slow query log with TTFB measurement and shows which queries really dominate the response time.
The N+1 Problem: Many Small Queries Instead of One
The N+1 problem arises when an application first loads a list (1 query) and then issues another query for each entry in the list (N queries). A category page with 20 products thus loads not 1 but 21 queries -- and with every additional relation, such as the manufacturer or the review per product, the number multiplies. Each of these queries is harmless on its own, yet the sheer quantity creates latency, connection setup and processing overhead that drives database time up.
What makes the N+1 problem tricky is its invisibility in the code: an innocent-looking loop over products triggers hundreds of queries in the background, without any single one standing out in the slow query log. Only the sum hurts. The solution is called eager loading: instead of N individual queries, you preload the related data in a single, bundled query -- typically via a JOIN or a preceding collective SELECT with an IN clause.
-- N+1: first the list, then one query per product
SELECT id, name FROM products WHERE category_id = 42; -- 1 query
SELECT * FROM prices WHERE product_id = 1001; -- + N queries
SELECT * FROM prices WHERE product_id = 1002;
-- ... 18 more
-- Eager loading: everything in one bundled query
SELECT p.id, p.name, pr.amount
FROM products p
JOIN prices pr ON pr.product_id = p.id
WHERE p.category_id = 42;N+1 Likes to Hide in Templates and Lists
Indexes: Avoiding Full Table Scans
Even a single query can be slow if the database has to check every row of the table to see whether it matches the condition -- a full table scan. With a few thousand rows this barely shows, but with a grown product catalog of hundreds of thousands of records it becomes a brake. An index works like the index of a book: instead of leafing through every page, the database jumps straight to the matching spot. Indexed queries often examine only a handful of rows instead of the entire table. Since web.dev classifies a TTFB above 1.8 seconds (web.dev) as poor, this very difference often decides whether a page still reaches the good range.
The art lies in indexing the right columns -- typically those that are filtered on (WHERE), sorted by (ORDER BY) or joined on (JOIN). Composite indexes covering several columns in the right order are especially effective, for example category and status together. Indexes are not an end in themselves, however: each index must be maintained on every write and costs storage. Too many or poorly chosen indexes slow down inserts and updates without bringing read benefits. The rule is to index deliberately on demand, not across the board.
WHERE Columns
Columns that are filtered on belong indexed. Without an index, the database checks every row; with an index, it jumps straight to the matching hits.
JOIN and Sort Columns
Join and ORDER BY columns benefit strongly from indexes. They avoid expensive sort and matching operations across the entire table.
Composite Indexes
Several columns in the right order cover combined filters. The column order decides which queries can use the index.
EXPLAIN: Reading the Execution Plan
Whether a query uses an index or scans the whole table is revealed by the EXPLAIN command. It presents the execution plan in advance: which tables are read in which order, which index is used, and how many rows the database estimates it will check. EXPLAIN is thus the most important diagnostic tool of query optimization -- it turns assumptions into evidence-based decisions. Whoever adds an index should compare the EXPLAIN plan before and after to confirm the effect.
-- Show the plan of the query
EXPLAIN SELECT id, name FROM products
WHERE category_id = 42 AND active = 1;
-- Warning signs in the output:
-- type = ALL -> full table scan (no index used)
-- key = NULL -> no index selected
-- rows = very high -> many rows are examined
-- Goal: type = ref/range, key set, rows lowThree fields deserve special attention. The value type = ALL signals a full table scan -- the database reads the complete table because no suitable index exists. An empty key field confirms that no index was chosen. And a high rows value shows how many rows the database is likely to check. If this value drops drastically after creating an index, the index was spot on. This makes optimization traceable rather than accidental.
Practical Tip: Test Against Real Data Volume
Query Caching: Absorbing Recurring Load
Some queries can be sped up, others are best avoided entirely. Much data in a shop rarely changes -- the category structure, manufacturers, product master data -- yet it is queried anew on every page view. Query caching stores the result of a query in fast memory, so that identical follow-up requests are answered without renewed database work. A cache hit answers in a fraction of the time a real query needs -- and at the same time relieves the database for the queries that really need fresh data. The commercial lever is considerable: in the travel segment, conversions rose by as much as 10.1 percent (Google/Deloitte, 2020) per 0.1 seconds of faster mobile load time.
What matters is the right layer. Application-side caching of frequently needed results in an in-memory store is usually more effective than the database-internal cache, because it skips not only the query result but the entire processing path. How such caches combine with tools like Redis and a reverse proxy like Varnish is explored in our article on caching strategies with Varnish and Redis -- database optimization and caching mesh cleanly here.
Do Not Forget Cache Invalidation
Query Optimization in Everyday Shop Life
In e-commerce, the biggest levers appear at recurring spots: the home page with its teasers, the category page with filters and sorting, the product page with variants and reviews, and the checkout with stock and price checks. Filter and sort queries over large catalogs in particular benefit strongly from suitable indexes, while list displays are classic N+1 candidates. The order of optimization follows the total time from the slow query log: first the queries that cost the most in sum. That the effort pays off shows in behavior: faster pages keep users longer, and in retail visitors spent around 9.2 percent (Google/Deloitte, 2020) more on faster sites.
For server-rendered shops based on Shopware CE, it pays to look at the most frequent page types individually and measure the query count per page. If it drops from several hundred to a few dozen, that is usually due to resolved N+1 patterns. Which further levers apply in the shop context is explored on our page about Shopware performance. Database optimization forms the foundation there: without fast queries, even the best frontend tuning remains piecemeal.
| Problem | Symptom | Diagnosis | Measure |
|---|---|---|---|
| Full table scan | Single query slow | EXPLAIN shows type=ALL | Add a suitable index |
| N+1 queries | Many small queries per page | Query counter, slow query log | Eager loading via JOIN/IN |
| Repeated load | Same query very often | Frequency in the log | Query caching with invalidation |
| Wrong index | Writes slow | Check unused indexes | Remove superfluous indexes |
| Large dataset | Grows over time | rows value in EXPLAIN | Indexes and pagination |
The fastest query is the one that is not made. Eager loading first clears away unnecessary N+1 queries, then indexes ensure that the remaining queries examine few rows instead of all -- and query caching absorbs what rarely changes anyway.
Measure, Safeguard and Stay Fast for Good
Query optimization is not a one-off project but an ongoing concern, because datasets grow and new features bring new queries. A query that is fast today can become a brake tomorrow with a tenfold catalog. That is why the slow query log belongs permanently enabled and regularly evaluated, ideally paired with TTFB monitoring per page type. This makes regressions visible before users feel them. This is also commercially relevant: the probability of a bounce rises by 32 percent (Google/SOASTA, 2017) as load time goes from one to three seconds.
We combine the evaluation of slow query logs and EXPLAIN plans with continuous TTFB measurement to separate database time from pure network latency. Only this separation shows whether a high TTFB stems from slow queries or from distance to the server. Since 53 percent (Google, 2018) of mobile visits are abandoned when loading takes longer than three seconds, this observation is not a luxury but part of our performance services, which look at database, server and frontend together.
The Core in One Sentence
The effort pays off multiple times. A low TTFB improves First Contentful Paint and Largest Contentful Paint and thus the Core Web Vitals, which strengthens organic visibility. Faster queries relieve the database, lower server load and stabilize response times even under load spikes. And they act directly on revenue: a mobile load time 0.1 seconds faster increased retail conversions by 8.4 percent (Google/Deloitte, 2020). Two further levers are closely related: mobile performance optimization, which shows how the faster TTFB pays off on mobile connections, and the targeted steering of the loading process with resource hints like preload and prefetch, which start after the database work. The foundation, however, is laid by server optimization, which addresses database time first.