WooCommerce analytics runs up to 83x faster on columnar copy
WooCommerce analytics ran as much as 83 times faster on a column-oriented analytical database replica than on a transactional storage engine in benchmarks conducted by Percona, showing the potential benefit of separating analytical reporting from an online store’s transactional database. The largest gains appeared in reports that grouped orders by both time and product category, although important compatibility and accuracy differences remain.
The underlying distinction is how each engine arranges data. The transactional storage engine is row-oriented, meaning it stores the values belonging to one record together. That suits Online Transaction Processing (OLTP), the short reads and writes used for activities such as placing an order. Online Analytical Processing (OLAP) instead scans, groups and summarises large numbers of records. A columnar engine stores each field separately, allowing a report to read only the columns it needs, compress similar values more effectively and process data in batches.
The analytical database is a portable system comparable in approach to a lightweight embedded relational database. Implementations from a community-developed database project and Alibaba have made it possible to use the analytical database as a storage engine for the open-source database server, so a table declared for the analytical engine is stored in columnar form. One proposed architecture keeps the live shop on the transactional storage engine and uses asynchronous replication from the open-source database server, which continuously copies database changes, to maintain an analytical replica for reports.
Percona tested that design with WooCommerce, which it says is used by around four million stores and between 20% and 39% of ecommerce websites. The generated test store contained 400,000 orders and 5,000 products, loaded with the WooCommerce Smooth Generator plugin. WooCommerce normally avoids reporting directly from live orders. It maintains seven denormalised lookup tables plus its order statistics table, a one-row-per-order table holding dates, status, customer, item counts and monetary totals. A scheduled background process must keep this derived data current.
Both databases ran in Docker containers on the same host. The source used Percona’s distribution of the open-source database server, version 8.0.46, with the transactional storage engine and a 4 GiB buffer pool. The replica used version 8.0.44-2 of Alibaba’s branch of the same server, based on upstream version 8.0.44 and the latest available version at test time. Its analytical database memory limit was 4 GiB, its thread setting used all system CPU cores, and a forced-conversion setting changed tables created through replication or the server’s dump utility from the transactional engine to the analytical engine. The transactional database directory occupied 7.3 GiB, while the analytical copy used 601 MiB, approximately one twelfth of the space.
WooCommerce presented a routing obstacle because WordPress uses one global database connection for both shop activity and analytics. Percona therefore copied the entire WooCommerce installation into another directory, changed only its database host, and connected that copy to the replica. It received a separate PHP pool, socket and virtual host on an internal port. Requests to the normal installation reached the transactional storage engine, while requests to the copied installation ran the identical application code against the analytical database.
For each report, Percona made several attempts and retained the fastest result. The engines did not run concurrently: every report ran first on the transactional storage engine and then on the analytical database. Results were serialised, sorted and compared before timing was accepted. Requests went through WooCommerce’s Analytics REST API with forced cache refresh, which required the database to calculate every response instead of returning a cached answer. Queries using syntax specific to the source database server that failed on the analytical database were excluded rather than modified.
Built-in reports covered 1 January 2024 to 1 September 2026. Statistical reports grouped results by month, while detail reports returned no more than 100 rows per page. Every accepted built-in report was faster on the analytical database, with improvements from roughly 10 times to almost 63 times. The analytical database completed them in less than one second, compared with five to 16 seconds on the transactional storage engine. Category reports produced the largest gains because of their extensive grouping and aggregation.
Segmented reports, which divide totals by hour, day, week or month and can further split them by dimensions such as category, widened the difference. The analytical database was about 14 to 83 times faster. Category and time segmentation pushed the transactional storage engine into multi-minute execution, while the analytical database responded in seconds. Percona also tested all 11 reports from Coddium’s Customer Analytics for WooCommerce plugin, including lifetime value, churn, RFM segments, cohort retention, repeat purchases, order sequence and acquisition sources. Without changing the plugin, the analytical database was about two to nearly 70 times faster.
A direct query against live order tables counted items by product category, billing city and day of the week. Across three attempts per engine, the analytical database consistently took about 4.1 seconds, while the transactional storage engine needed approximately four minutes and 16 seconds. The result had two additional groups on the analytical database because the engines handled text differently. The transactional engine applied the city column’s case-insensitive and accent-insensitive collation, the rules used to compare and sort text, while the analytical database compared the underlying bytes and kept accented and plain spellings separate. No orders were omitted.
That behaviour is one of several deployment limitations. Source database syntax such as casting a value as an unsigned integer type can fail because the analytical database does not recognise the type. Queries may require rewriting, potentially through a query-rewriting proxy or the database server’s rewrite plugin. Grouping and joining on numeric identifiers rather than text can reduce collation differences. A distinct-city test returned 114,412 values on the analytical database and 114,375 on the transactional storage engine; forcing binary comparison on the transactional engine also produced 114,412.
The analytical database also calculated decimals as double-precision floating point by default. Some report totals therefore differed by less than one cent. Percona considered that precision sufficient for reporting, but acknowledged the issue for real-money calculations.