The database is the heart of every online shop -- every product view, every search and every checkout triggers queries. As the shop grows, so does the database: the median web page grows 8.4 percent every year to 2.6 MB (HTTP Archive, 2025), and behind the scenes order data, carts and logs multiply. Without regular maintenance, this leads to slow queries, longer load times and a higher risk of outages. Customers are sensitive to this: 53 percent of mobile users abandon a page if it takes longer than three seconds to load (Google). This article shows how professional database maintenance -- index optimization, slow-query analysis, cleanup, backups and growth monitoring -- keeps your shop permanently fast and stable.
Why the Database Determines Shop Performance
Every dynamic page of an online shop is built from database queries. The homepage loads categories, promotions and bestsellers, a product page fetches master data, variants, prices and reviews, the checkout aggregates cart, shipping rules and tax rates. If the database is slow, the entire page is slow -- regardless of how well the frontend and caching are optimized. Speed has direct business consequences: a delay of just one second can reduce the conversion rate by 7 percent (Akamai). For every additional second of load time between 0 and 5 seconds, the conversion rate drops by an average of 4.42 percent (Portent, 2019).
The problem intensifies with growth. A query that responds in milliseconds with 10,000 orders can take several seconds with 500,000 orders -- especially if it has to scan the entire table without a suitable index. German e-commerce grew 3.9 percent in 2025 to 92.3 billion euros (HDE), and successful shops accumulate more data every year. This is exactly where proactive maintenance comes in: it keeps response times consistently low even as the data volume grows. Without it, performance degrades gradually -- often unnoticed, until a bottleneck hits during the holiday season.
The core in one sentence
Index Optimization: The Biggest Lever for Fast Queries
Indexes are the most effective lever for query performance. Without a suitable index, the database has to scan the entire table for a search -- a so-called full table scan. With an index, it jumps directly to the relevant rows via a B-tree structure (MySQL documentation). The difference is dramatic: in a documented example with a table of 13 million rows, an index reduced the query cost from around 1,205,103 to roughly 2,151 -- an improvement by a factor of 560 (Percona). On large order or product tables, a single missing index often decides between milliseconds and several seconds of response time.
The art lies in the right balance. Indexes speed up reads but slow down writes and consume memory (MariaDB documentation) -- every index must be maintained on each INSERT, UPDATE and DELETE. Over-indexing is therefore a real cost factor. Professional index optimization analyzes both: missing indexes on columns that are frequently filtered, sorted or joined on, and unused indexes that only cost write load and storage without ever speeding up a query. With composite indexes, the column order is additionally decisive, because the database only uses the leading columns of the index definition.
Indexes are not a free lunch
Finding and Fixing Slow Queries
Before anything can be optimized, the slow queries must be made visible. The most important tool for this is the slow query log. In MariaDB and MySQL it is enabled via slow_query_log = ON; with long_query_time = 1, all queries taking longer than one second are logged (MariaDB documentation). Particularly insightful is the log_queries_not_using_indexes option, which specifically captures queries running without an index -- because these can usually be fixed with a new index or a rewrite (MariaDB documentation).
[mariadbd]
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = ON
log_slow_admin_statements = OFFThe raw log file is only the beginning. Tools like mysqldumpslow or pt-query-digest summarize the entries and show which queries take the longest and how often they run (MariaDB documentation). This combination of duration and frequency is decisive: a query that takes 0.8 seconds but runs on every page load has a greater overall impact than one that takes two seconds but only runs once an hour. In the database audit we therefore prioritize by total load -- fixing the queries with the biggest lever first, instead of blindly optimizing every slow query.
Slow Query Analysis
Enabling the slow query log and evaluating by duration times frequency -- the queries with the greatest total load are addressed first.
Index Optimization
Add missing indexes, remove unused ones, create composite indexes with the correct column order -- targeted rather than indiscriminate.
Data Cleanup
Regularly remove old carts, logs and version data so the largest tables do not grow unchecked.
Query Optimization
Rewrite slow queries, review EXPLAIN plans and resolve unnecessary JOINs or subqueries where an index alone is not enough.
Backup and Restore Test
Regular database dumps, verified recoverability and documented recovery times instead of backups taken on a hope.
Growth Monitoring
Continuously observe table sizes, query times and data growth to detect bottlenecks before they become a problem.
Data Bloat: Why Shop Tables Grow Uncontrolled
A large part of the database load comes not from productive data but from bloat. In Shopware, the cart table (cart) is typically one of the largest tables in the entire database (Shopware Store), because every visitor -- even without intent to buy -- generates a cart entry. Add to this log entries, version data (version_commit, version_commit_data) and queue tables (enqueue) that grow continuously without regular cleanup. Shopware provides scheduled tasks that automatically delete superfluous and obsolete data daily, freeing up storage (Shopware Store).
How critical this is becomes clear from a detail in Shopware development itself: the original SQL for deleting outdated cart entries used no database indexes, which on high-traffic shops led to query times of more than 30 seconds (Shopware Developer). Such delete operations can slow down the entire shop if they run at the wrong time. A well-designed cleanup concept therefore removes bloat regularly in small, index-supported steps -- instead of rarely in large blocks that trigger locks and block the database. The result: smaller tables, faster queries and lower storage requirements.
| Table / Data type | Typical problem | Cleanup approach |
|---|---|---|
| Carts (cart) | One of the largest tables, grows with every visit | Delete outdated carts regularly and index-supported |
| Log entries | Grow without limit if not cleaned up | Define retention period, remove older entries |
| Version data | Accumulate with every data change | Scheduled cleanup of old version states |
| Queues (enqueue) | Processed messages remain | Remove completed entries after a defined period |
| Search indexes / cache | Stale entries distort results | Regular rebuild and cleanup |
| Guest sessions | Accumulate unused | Automatically remove expired sessions |
Backups: Only a Tested Backup Is a Backup
Database maintenance always includes data protection. The database contains a shop's most valuable data -- orders, customers, invoices -- and its loss can be existential. The Bitkom study 'Wirtschaftsschutz 2025' shows that 87 percent of German companies were affected by data theft, espionage or sabotage in the past twelve months; the total damage to the German economy amounted to 289.2 billion euros (Bitkom, 2025). A reliable backup is the last line of defense -- against hardware failures, faulty updates, attacks and accidental deletion alike.
What matters is not the creation but the recoverability. A backup that has never been tested is not a backup but a hope. Database maintenance therefore includes regular restore testing: the backup is actually restored in an isolated environment, checked for completeness, and the recovery time is documented. The 3-2-1 rule has proven effective: three copies of the data, on two different media types, one of them in another location (Bitkom). With databases, two methods come together: regular full dumps and, for minimal data loss, point-in-time recovery via the binary logs. More on this in our article on the PHP lifecycle and safe upgrades, which also covers the safe handling of database migrations.
The most common backup mistake
Growth Monitoring: Spotting Bottlenecks Before They Show
Database maintenance is not a one-time project but an ongoing process. A shop that is fast today can be noticeably slower in a year -- not through a sudden change but through steady data growth. That is why maintenance includes continuous monitoring of the decisive metrics: size of individual tables, average and maximum query times, number of slow queries per day, buffer usage and the ratio of reads to writes. These values are observed over time so that trends become visible before they affect users.
The advantage lies in predictability. If the cart table grows by a fixed amount month after month, you can estimate when a cleanup interval needs to be shortened or an index adjusted -- long before customers notice a slowdown. If the average query time slowly rises, that is an early warning of a missing index adjustment. This data-driven approach turns database maintenance from reactive firefighting into proactive prevention. In addition, the entire server layer should be secured -- our article on server hardening for shops describes how, looking at PHP, web server and database access together.
- Record table sizes monthly and document the growth trend
- Continuously observe average and maximum query times
- Track the number of slow queries per day as an early warning metric
- Monitor the database buffer usage and cache hit ratio
- Log the backup size and last successful restore
- Define thresholds that trigger cleanup or index adjustment
The Structured Maintenance Cycle for Shop Databases
Effective database maintenance follows a recurring cycle rather than ad-hoc interventions. It begins with an assessment: how large is the database, which tables dominate, what do the slow-query statistics look like and which indexes already exist? From this analysis emerges a prioritized action plan. Optimizations are then implemented -- each tested first in a staging environment, because a changed index structure or a large cleanup can in rare cases have unexpected side effects. Only after a successful test is the change moved to the live system, accompanied by monitoring.
Then the cycle starts over: the effect of the measures is measured, the metrics are compared with the baseline, and new bottlenecks are identified. This continuous approach distinguishes professional continuous monitoring of the database from a one-time optimization that fades after a few months. We recommend a complete database audit at least once per quarter plus ongoing monitoring as part of regular maintenance. After major changes -- a relaunch, new features or a version jump of the shop system -- an additional audit is advisable, because query patterns and data volumes often change significantly then.
A well-maintained database goes unnoticed -- it delivers consistently fast responses while the shop grows. Only its neglect becomes noticeable, usually at the worst possible moment.
The economic benefit is measurable. Faster queries mean faster pages, and faster pages mean more conversions and more satisfied customers -- with a single second of delay, 7 percent of conversions are already at stake (Akamai). At the same time, a lean, well-indexed database lowers server requirements, because the same hardware can process more requests. And a tested backup concept reduces the risk of costly outages in an environment where 87 percent of companies are affected by security incidents (Bitkom, 2025). Continuous database maintenance thus pays off on three levels: speed, cost and resilience.