WooCommerce is the go to e-commerce plugin for WordPress so it’s very widely used. WooCommerce comes with it’s own set of challenges that can become worse over time, or as traffic to your site increases.
Knowing how to address these challenges involves knowing a bit about how WordPress works, a bit about how WooCommerce works, and a bit about how your hosting works. This post covers the problems you can run into when using WooCommerce, what causes these problems and how you can overcome them.
Different types of slow WordPress.
You get two different types of WordPress slow. A slow application and a slow render time in the browser. Most of the posts I’ve published on our blog are specific to rendering and optimising page output. When you’re using WooCommerce page output isn’t usually the problem, it’s more often the case that you have a slow application.
You can tell the difference between the slow application and slow rendering situations by comparing the load times of your site’s front end and back end. If the front end is slow, but the back end is OK, this is usually a rendering or page output specific issue. If the back end is slow, this is a slow application problem, rather than a page output problem.
So what is it about WooCommerce that makes WordPress slow? For this to make sense, we’ll have to take a brief step back in time to when WordPress was just for blogging.
WordPress used to be a blogging platform.
WordPress has kind of morphed into a “can do many website things” piece of software, but it was originally made for blogging.
On blogs, you have posts. When using WordPress these get put in the posts table in the database. Data about these posts (author, last edited time, category etc) gets put in the postmeta table in the sites database. The more posts you have, the more rows and data these tables contain.
Now WordPress is used to publish more than just blogs.
This “putting content in the posts table”, and “data about the content in the postmeta” table still happens with your website’s page content even though you’re not necessarily writing a blog. The content of pages also gets put in the posts table, for example.
Commonly used WordPress database tables.
Posts and postmeta
Often referred to as wp_posts and wp_postmeta.
Although your pages and your blog posts are in different locations in your wp-admin, both still have their content stored in the posts table, and data is about both posts and pages is put in the postmeta table.
Consequently, any time page content loads, your website’s database’s posts and postmeta tables are involved.
Options
Often referred to as wp_options.
The options table of your site’s database is really important. Your site’s URL is defined in the options table (along with a lot of other fundamental information). Pretty much every time any part of your website loads (even wp-admin) data in the options table is involved.
Why use the database to store data like this?
This is really a programming thing. Usually, your program (the code it operates) will be relatively static. The program works how the program works, so to speak.
As people need to be able to use the WordPress “program” for their websites, there’s some variation between each individual WordPress installation. This variation is what programmers call variables.
All the variables get put in the database. All the code goes in the .php files you see in your hosting’s file manager.
This separation between code and variable information separates variable information from logic.
This separation helps make things like updating and maintenance easier, while still providing all the lovely things we like about WordPress. Or don’t as the case may be.
SQL Databases and big tables.
The next part of this post goes on quite a lot about databases and their size, and the size slowing things down.
This might sound very much like SQL databases aren’t very good for applications that have big databases. So…
What’s considered a big database?
It depends a lot on your hosting. If you’ve got your own server you can do things like dedicated RAM to MySQL, but if you’re operating in shared hosting, this isn’t a config you have access to. Shared hosting also has to take multiple users in to account, so there has to be some limitations to stop one user consuming all the RAM, and causing problems for other users.
In shared hosting ballpark sizes arre:
- 100MB is average
- 200MB is large
- 300MB is big
- 400MB very big
- 400MB+ You need your own server
It’s VERY important to consider that databases grow in size over time. Orders, products, customer accounts… they’re all saved in the database. As more orders are taken and more customers sign up, more data is written to the database. Consequently, data storage increases over time, and given enough time, querying this data will become problematic.
Are SQL databases just a bad idea for large data sets?
SQL databases are not a bad idea for large data sets. In fact, they’re often the best choice, depending on how you use them. How. You. Use. Them.
SQL databases (like MySQL, MariaDB, PostgreSQL) handle large datasets well when:
- Tables are properly indexed
- Queries are well written
- Data is structured and relational
- You need joins, constraints, and consistency
The problem when you’re using a CMS like WordPress with WooCommerce is that you don’t control a lot of this. The queries, structuring of data, joins, constraints, and consistency are all specific to WooCommerce (in the context of this blog) so all you can really do is the “tables are properly indexed” part.
Did you know you could do that? Do you know how? Do you know what needs to be indexed? You might, but would you be reading this post if you knew about this already?
Indexing database tables.
This is covered in a practical how to toward the end of this blog post.
If you have big database tables that aren’t indexed properly things will be slow.
Indexing is a way of helping the database find data faster without having to scan every single row in a table each time a query is run. It works a bit like the index in a book. Rather than reading every page to find a topic, the database can jump straight to the relevant entries using the index structure.
When you add an index to a column, the database builds a separate, organised lookup structure for that column’s values, which makes searches, joins, and filtering much quicker. The trade-off is that indexes take up extra space and slightly slow down writes like inserts and updates, because the index also has to be maintained whenever the data changes.
How do you index database tables?
It’s possible to index tables using MySQL commands directly. In shared hosting you’d usually do this using a tool built in to your hosting such as PHPMyAdmin. You can also use PHPMyAdmin’s interface to index tables without using commands.
There are also plugins available that can be used to index tables, such as index wp mysql for speed (see what they did there?).
Always backup your database before indexing, as adding indexes changes the database structure.
How do you know what to index?
If you’re writing your own code, or have root access, you know what to index by looking at how your data is queried. You index the columns that your database searches, filters, sorts, or joins on most frequently. For example, if you often run queries like SELECT * FROM orders WHERE customer_id = 123, it makes sense to index the customer_id column because that’s what the database uses to find rows. Columns used in JOIN conditions, WHERE clauses, ORDER BY, and GROUP BY are typically good candidates for indexing.
You don’t usually index every column, because each index takes extra storage and slows down inserts, updates, and deletes. The database has to keep the index up to date whenever data changes. A common strategy is to analyse slow queries , see which columns the queries filter on or sort by, and create indexes there. Essentially, you’re giving the database shortcuts for the paths it walks most often.
For WooCommerce, indexes are usually added to things like product IDs, order IDs, and postmeta keys that are queried frequently, because those are the columns that store data used in searches, reports, and joins. You need to know a bit about where WooCommerce stores data, and how it queries data, to know what to index.
WooCommerce and posts and postmeta database tables.
WooCommerce has a lot of data to store. There’s products, product variants, orders, order items, customers, coupons, cart, sessions, and analytics.
In older WooCommerce installations, or installations with HPOS disabled (more on HPOS later), a lot of this data gets stored in the posts and post meta table. Products, product variants, orders, and coupons all get stored in the posts and post meta table.
Over time, unless you clear down older orders, your posts and postmeta tables grow, and grow and grow. These are also the tables in which page and post data are stored. What this means is that over time, even if the pages on your website remain unchanged, the tables in which page data is stored increase in size.
WooCommerce and transients.
A transient is a type of caching where data is stored in the database for a fixed period before it gets automatically deleted. This data can be quickly accessed, helping to save time and enhance the performance of your website.
A common example is the shopping cart contents. These are stored using transients so that a customer can leave the cart page and return later to find their items still there.
On-sale products, featured products, out-of-stock counts, low stock counts, sales reports, customer reports, product variation prices, product child/variation relationships, related products, product weight/dimensions checks, shipping rates, shipping zones cache, tax rates, tax classes, coupon data, geolocation (GeoIP) lookups, webhook delivery logs, general WooCommerce cache entries, system status info, WooCommerce Blocks data, Store API responses and cart fragments are all transients WooCommerce generates.
WooCommerce store transients in the options table of your site’s database. That’s right, the table involved with every page load.
WooCommerce and HPOS (high performance order storage).
To be fair, WooCommerce haven’t sat on their hands about these problems, they have developed a solution. That solution is called HPOS (high performance order storage).
HPOS, if enabled upon installation, puts order data in some WooCommerce owned table, rather than posts and postmeta:
- _wc_orders
- _wc_order_addresses
- _wc_order_operational_data
- _wc_orders_meta
What this does is separate WooCommerce order data, from page data, so it removes the risk of order data being queried in page load type activities. This separation is really quite a sensible thing to do.
If you didn’t enable HPOS when you installed WooCommerce, you can switch to it. Please read WooCommerce’s documentation before trying this, and take a backup of your database.
Switching to HPOS involves running your store in sync mode for a while. When running in sync mode, order data is copied from posts and postmeta to the WooCommerce tables mentioned above. This syncing can take time, and during this time, the data specific to a certain order could be either in one table or both.
The “could be in both” is by design, so there’s always somewhere an order can be found. WooCommerce has to sync all order data from posts and posts meta, into the WooCommerce specific tables, before the switch to HPOS can be made in full, and the order data removed from posts and postmeta.
During the sync period things can be a bit slow, due to the replication of data across multiple tables. You end up with 6 big tables for a while instead of just two, while the syncing is taking place.
Once the sync is finalised, you switch to HPOS in full, and then order data isn’t held in the same place that page data is held.
You can then index your WooCommerce specific tables for a more specific optimisation.
WooCommerce is a good e-commerce plugin!
WooCommerce is one of the better e-commerce plugins. It’s very widely used, it’s versatile, it’s got lots of add-on functionality via its ecosystem and it has a plethora of tools that can be used to maintain your WooCommerce installation.
WooCoomerce also makes use of technologies that are commonly available, and in some cases free (such as WordPress).
While this post might sound a bit like I’m basking WooCommerce this isn’t my intent.
WooCommerce storing large amounts of data in posts and postmeta tables isn’t some kind of bananas thinking. This is pretty much how WordPress is supposed to work. These tables are where it’s generally advisable to put data that’s presented to website users or visitors (and you want customers to be able to do things like see their orders).
Some blogs have hundreds of thousands of pages, so you would think it would be OK to have hundreds of thousands of products/orders/customers. The problem really occurs when you get into how this data is used, and how the data being stored over time increases.
The key point with all this is really that when you start using WooCommerce (or any e-commerce plugin for that matter) you move from the realm of being able to “work things out in wp-admin” to knowing how to use your hosting to get the most out of WooCommerce, or at least avoid some common problems.
WooCommerce don’t know how big your store is going to be, they don’t know how many product variants you’ll use, they can’t change that WordPress uses SQL databases.
WooCommerce are doing a great job. It’s all here, HPOS, clean up tools, tables that can be indexed… but it is ultimately up to you to make use of these facilities, and WooCommerce can’t do that for you.
The problem with HPOS.
WooCommerce has it’s own plugin ecosystem. This is similar to the WordPress plugin ecosystem, it’s just WooCommerce specific and managed by WooCommerce, and these plugins all only work with WooCommerce. Whilst this is a super handy thing to have for situations like “we really need to integrate a bar code reader with our store”, there’s also a development and versioning implication.
It the plugins in the WooCommerce plugin repo aren’t kept up to date, they might not be compatible with HPOS. They still reading and writing order data to and from posts and post meta, instead of the WooCommerce specific order tables that HPOS creates.
There is a compatibility check in the HPOS options in WooCommerce, so at least you can tell if you’re going to have some kind of problem when switching to HPOS.
The problem is that if there isn’t an update for a plugin that isn’t compatible with HPOS, it’s not going to work with an HPOS enabled WooCommerce setup. You’re really then faced with either not gaining the separation of data that HPOS provides, or removing the plugin to be able to switch to HPOS.
If you don’t enable HPOS because of a plugin that’s not compatible, that is a bit like passing up on the optimisation that WooCommerce have developed to help address known performance issues.
WooCommerce and transient clean up tools.
The good news is that WooCommerce has transient clean up tools.
You can also schedule the action to delete expired transient files for running immediately. The expired transient files are cleaned every 24 hours. When HPOS is enabled, some further tools become available.
By default, WooCommerce stores transients in the options table. The options table is very integral to WordPress (your site’s URL is defined in the options table, for example). So these transients can be high in number, and get put in a place your site uses a lot. So you’ve got “big” in “often”. Not the best mix.
You can negate this “storing in options table” business pretty easily, by using object caching, so using this is a good idea when using WooCommerce. That said, you do need to know this, you do need to bring object caching into effect and to be able to do that, it needs to be available in your hosting.
This is all great stuff, but…
The problem with transient clean up tools.
Even with these clean up tools, the automatic clean up job and transients being lazily expired, they can still build up in a WooCommerce site’s database.
Transients can build up in a WooCommerce/WordPress database for a few common reasons. They’re not always aggressively cleaned, and something in the system is preventing or delaying cleanup.
In WordPress they’re handled by the Transients API (core functionality of WordPress, not just WooCommerce).
A lot of this relies on wp-cron, so if there’s a problem with that, things can mount up. Normally, you’d be advised to set up a true cron job to trigger wp-cron, but you have to know to do that, rather than everything defaulting it being OK.
WooCommerce and extensions heavily use transients for:
- product queries
- shipping rates
- API responses
- fragments (cart fragments, etc.)
More traffic + more plugins = more transient entries created… and there’s a lot of automated traffic at the moment… following all those featured product links and so on.
Really, what I’m saying here, is that unless you know your setup is running as it should, you can’t leave it on its own and everything be fine.
In essence, you need to know a bit about how this works to be able to set things up in a way that won’t cause a problem that you didn’t know existed until your website fails. Which is pretty much why I’m writing this.
Website fails? Yep. Big fat “error establishing database connection” message, across the whole of your site. That’s the worst case scenario.
Here’s the thing, it’s not actually that difficult to achieve. All you really need is no HPOS, no object cache, a reasonable amount of products with a reasonable amount of product variants, a 404 on your pages, and some bots to crawl your site. Pretty day to day stuff, then.
Where things start going wrong.
As you’re probably getting the picture by now, big can equate to slow.
Big can also equate to “I’ve run out of RAM” even if you have your own server.
If you’re hosted on anything Linux based (which most people are) when you run out of RAM, processes are killed. This is by design, and things work like this to prevent things like data corruption (that can be worse than your site going down) and to allow other services to function. This is all managed by a service called the Out of Memory Killer or OOM killer.
Unless you tinker with OOM killer (by adjusting things like oom_score_adj), MySQL is a common candidate to be killed when RAM is exhausted. In a typical web hosting environment, MySQL is often one of the most resource heavy processes, which makes it more likely to be temrminated by the OOM killer. As your site needs to read from a database to serve page content, if MySQL is killed, your site will usually fail with an “error establishing database connection” message.
Why does RAM usage get so high?
Quick recap:
- No HPOS = Lots of WooCommerce data in posts and postmeta tables (where your site’s pages are held)
- No object caching = Transients in the options table (used on every page load)
- Products x variants = potentially lots of transients.
WordPress has this functionality, where if a page address is requested that doesn’t exist, WordPress will try and serve the closest page that’s available. I don’t really know why it does this, but I’d hazard a guess at misspelt URL mitigation or some kind of better SEO effort. It doesn’t really matter why, what’s important is that it does.
When WordPress “guesses” URLs and runs canonical redirects, it isn’t free. WordPress still has to query the database to figure out what the closest match is.
In a WooCommerce store with a reasonable number of products, lots of product variations, no object caching and no HPOS all those extra lookups start to add up.
Every request can trigger additional database work.
When WordPress tries to resolve the requested URL, it may run extra queries to, find matching slugs, resolve canonical URLs, load product data, load variations (which live as separate rows in wp_posts and wp_postmeta), and hydrate WooCommerce objects (products, variations, metadata).
Without object caching these queries are repeated on every request. Nothing is reused between requests.
Without HPOS orders and related lookups still add load to the same core tables so the wp_posts and wp_postmeta tables become hotspots.
Now combine that with:
- WooCommerce product queries (often heavy joins on postmeta)
- Variation-heavy products (each variation = extra rows)
- Canonical URL resolution (extra queries per request)
- No caching layer to absorb repeated reads
And you get a high number of database queries per page load, many of which are repeated and expensive, which can be to the degree of RAM running out, and OOM killer killing mysql.
Avoiding this isn’t that difficult (cough! object caching, cough!), but you do need to know to configure object caching to do that. If you don’t know to do this, or approach this situation like “this has all been sorted out for me” you could end up in a very “not sorted out” situation. And your site down. When one bot hit a 404.
Other “big database” problems…
Wildcard SELECTs (e.g. LIKE ‘%term%’)
A wildcard query tries to match patterns rather than exact values, for example:
SELECT * FROM wp_posts WHERE post_name LIKE '%shoe%';Wildcard SELECTs are expensive on large tables because indexes often can’t be used efficiently and if the wildcard is at the start (%term), the database usually cannot use an index this forces a full table scan. A full scan of a table that could potentially contain a few years worth of historical orders.
What this means is that every row must be checked, and performance becomes O(n) relative to table size.
Full scans scale poorly on large tables like posts and postmeta when full of historical order data. A wildcard search may require scanning hundreds of thousands or millions of rows. This is CPU and disk I/O intensive as each row’s value must be evaluated against the pattern.
So just by having a lot of data in posts and postmeta you risk the above taking place.
The problem with WordPress (and it’s glorious plugin ecosystem) is that it’s not always transparent whether another plugin (not WooCommerce) that’s not been written in the most forward thinking way, is going to do some kind of Wildcard SELECT on your posts and postmeta table when it’s trying to do something specific to page output.
Plugin X can potentially induce a problem that’s really been caused by a combination of lots of order data, and no HPOS.
Joins
Joins combine rows from multiple tables based on related columns:
SELECT *
FROM wp_posts p
JOIN wp_postmeta pm ON p.ID = pm.post_id;Joins can be expensive due to row multiplication.
If wp_posts has 100,000 rows and wp_postmeta has 500,000 rows, a join may need to match many rows per post and produce a large intermediate dataset.
A large intermediate result might cause a database to build temporary tables in memory or disk and/or sort or hash large datasets.
This adds memory pressure and disk I/O overhead.
Poor indexing amplifies the cost of this type of activity, as Joins rely heavily on indexes like: post_id and meta_key. Without proper indexing the join may degrade into nested loops and complexity can approach O(n × m). That’s a runaway train of Joins!
WooCommerce relies heavily on joins such as:
- Products ↔ postmeta
- Orders ↔ metadata
- Variations ↔ parent products
Because variations are stored as separate posts and attributes are stored in postmeta. This means asingle product page can trigger multiple joins across large meta tables.
The combined effect of Joins and wildcard SELECTs in real-world systems.
When wildcard searches and joins are combined:
- Wildcard filters reduce index efficiency
- Joins expand the dataset being processed
- Large meta tables increase join cost
- Repeated queries (due to no object cache) multiply the load
This becomes a problem at scale because in a large WooCommerce store:
- Many products × many variations
- Heavy reliance on wp_postmeta
- Frequent joins for product + variation + attributes
- Occasional wildcard queries
All of this adds up to increased query time, high CPU usage, memory consumption, slower response times (and potential timeouts), potential database saturation under load, and in extreme cases, RAM being exhausted and MySQL being OOM killed.
Again, you don’t know if some other plugin is going to do a join on your posts and postmeta table when it’s trying to do something specific to page output. It’s maybe not as likely as a Wildcard SELECT, but you can’t rule it out… and if you’ve got a lot of data in posts and postmeta…
Plugin X can potentially induce a problem that’s really been caused by a combination of lots of order data, and no HPOS. It’s not plugin X’s fault. It hasn’t been coded around the use of big database tables.
Why considering database queries and 404s is important.
When you operate with big database tables, you add a risk to your WordPress. The risk is that a join or a wildcard select will be run by some other plugin in your WordPress on this large amount of data.
This WILL slow your site down.
A WordPress installation is collection of code bases written, maintained band updated by a variety of parties.
If one party doesn’t take into account the you might be operating using large database tables, you do risk things being slow.
Minimising this risk either consists of either:
Managing your database tables.
Or
Not using plugins that run wildcard selects or joins a the large tables, and ensuing there’s no 404s in your page output.
While you can control your page output to cover the 404s this becomes a “monitor an update” effort on your part.
You can’t control how developers code their plugins.
So really, if you don’t manage your database data, you are operating with a risk of things being slow, or possibly completely failing. That’s just how the logic plays out.
The other thing to bear in mind is that the data in these database tables isn’t static. As more orders are placed, products added and customers register, the more data is stored in the database, which again increases the risk of a “slow” problem.
Risks are again exacerbated by using shared hosting, simply because RAM and CPU limits are lower. Due to these lower limits things are likely to become problematic sooner (the size threshold for a problem to occur becomes lower – it’s just maths).
While it is possible to operate a WooCommerce based site in shared hosting, you’re more likely to run into a “slow” problem sooner, unless you manage your database data effectively. You’re really faced with a choice either obtaining more resources (to increase the size threshold at which a problem to occurs), or optimising your database data, to reduce the chance of a problem occurring.
Usually upgrading will involve moving from a shared hosting service to a VPS. A managed VPS is most likely what you’ll need unless you’re happy to undertake system administration and maintenance yourself.
How to deal with slow website problems caused by large WooCommerce specific tables.
Use HPOS (High Performance Order Storage).
WooCommerce’s own fix for a known problem. HPOS moves WooCommerce specific data out of commonly used WordPress tables into WooCommerce specific tables. This pretty much eliminates the risk of another plugin running some kind of wild card query on tables normally used to store page data, where WooCommerce happens to have stored large amounts of data.
Moving data into WooCommerce specific tables also means you can index JUST the WooCommerce specific data. Your trade off (indexes taking up extra space and slightly slowing down writes like inserts and updates) is then specific to JUST WooCommerce, not your entire front end.
The posts and postman tables are so commonly used, storing lots of data in these (when you could not do that by using HPOS) is a risk. Using HPOS mitigates a degree of risk.
WooCommerce have developed HPOS to address a problem. Not using it is a bit like driving down the motorway in second gear and wondering why your fuel economy is poor.
Deploy object caching.
Object caching is very easy to deploy, and commonly available.
Object caching stores data retrieved from or built using database queries in memory so it can be reused later. This reducing the need to repeatedly hit the database.
One of the big wins with object caching is how it handles transients. Without object caching, transients are stored in your site’s database (typically in the options table), which is accessed on every page load. As more transients build up, that table can become bloated and slow things down.
With object caching enabled, transients are stored in memory instead, reducing database load and avoiding unnecessary overhead on one of the most frequently accessed tables.
Fix 404s.
When you’re running a WooCommerce site without HPOS, larger amounts of data (orders, products, variations, etc.) are stored in the same (posts) table as your site’s pages.
When a 404 (not found) occurs because a page URL is requested incorrectly, WordPress doesn’t just give up; it tries to find the “next best fit” by querying that same dataset.
This introduces additional overhead. Every 404 can trigger extra database lookups as WordPress attempts to resolve the request. The more data you have in the posts table, the more work is involved, and the more resources your site consumes.
It sounds a bit pedantic; Fix your URLs, stop the 404s, etc. But now imagine this:
The 404 is in your site’s footer, so it’s triggered on every page load. At the same time, your site is handling a steady stream of requests (for example from Meta crawlers). Because each request takes slightly longer due to the 404 and the “next best fit” lookup, your website starts falling behind. Requests begin to queue, CPU load increases, memory usage rises, and eventually the site can fail under the pressure.
By identifying and fixing 404s, you remove that extra layer of work. Even without HPOS, this can significantly reduce load and help prevent these kinds of cascading performance issues.
Tidy up!
Sorry, I sound like your mum, don’t I? Seriously though, the more data you store in your website’s database, the more risk you have of things being slow or maybe even failing.
There are tools built into WooCommerce that can be used to clean up transients. https://woocommerce.com/document/understanding-the-woocommerce-system-status-report/tools/
If you’re using your site’s database to do any kind of analysis, such as forecasting, or historical financials, you might consider exporting data, then using this “off site”. There are also tools such as Xero that you can connect to your WooCommerce site which allow you to look at financial and order date off website.
Clearing down older orders, products you no longer sell, and redundant customer accounts periodically will help stop data accumulating over time.
Gain more resources.
Unfortunately, it’s not quite as straightforward as “pay more money to make the problem go away”, as much as we might wish it were.
In reality, there’s usually a balance between increasing available resources and optimising how your database is used. Used together, both approaches can be effective, but on their own they are often less impactful.
If you’re working with a large dataset, optimisation will only take you so far. At some point, the overhead of the data itself (even when optimised) may require an upgrade in resources.
While upgrading hardware can provide more RAM and CPU, it’s easy to get stuck in a cycle of upgrades if optimisation isn’t also addressed. In addition, resource upgrades have limits in what they can improve. For example, increasing RAM and CPU will have little to no effect on I/O-bound issues (such as inefficient queries or disk-heavy operations).
Index database tables.
I’ve saved this for last, because it’s epic, and the hardest to undertake.
Indexing is a way of helping your database find data faster.
Without an index, when the database runs a query, it may have to scan every row in a table to find what it’s looking for. On small tables this is fine, but as data grows, this becomes slow and resource intensive.
An index works a bit like the index in a book. Instead of reading every page to find a topic, the database can jump straight to the relevant rows using the indexed column.
When you add an index to a column, the database builds a structured lookup (usually a B-tree), stores references to rows based on the indexed column’s values, then uses that structure to quickly locate matching rows during queries
So instead of:
“Check every row and see if it matches”
It becomes:
“Jump straight to the rows that match”.
Indexing results in faster queries.
Indexes dramatically reduce the amount of data the database needs to scan, and they’re especially important for:
- WHERE clauses
- JOIN conditions
- ORDER BY queries
Indexing results in lower CPU and disk usage.
Because fewer rows are scanned:
- Less CPU time is needed
- Less disk I/O is required
- Queries complete faster under load
Indexing results in better scalability.
As your site grows:
- Indexed queries stay relatively fast
- Non-indexed queries get slower and slower
Indexing is critical for joins.
Indexes are especially important when joining tables.
Without indexes, joins can become extremely expensive (large comparisons).
With indexes the database can efficiently match related rows.
The downside of indexing.
Indexes are not free:
- They take up disk space
- They slightly slow down writes (INSERT, UPDATE)
- Too many indexes can actually hurt performance
What you need to aim for is targeted indexing, not indexing everything.
How to know what to index.
It’s advisable to index columns that are:
- Frequently used in WHERE clauses
- Used in joins (post_id, meta_key, etc.)
- Used in sorting (ORDER BY)
- Used in lookups (IDs, slugs)
How to index database tables, when using shared hosting (no root access).
Identify slow queries.
Use tools like the Query Monitor plugin to identify:
- Slow queries
- Duplicate queries
- Queries with high execution time
Add indexes via phpMyAdmin.
Most shared hosting includes phpMyAdmin, and using this you can:
- Open a table (e.g. wp_postmeta)
- Go to the Structure tab
- Add an index to columns like:
- meta_key
- post_id
Or run SQL (you’ll need to take your site’s database prefix into account, as this might not be wp_ ):
ALTER TABLE wp_postmeta ADD INDEX meta_key (meta_key(191));There are also plugins such as Index WP MySQL for Speed that can be used to add indexes in a more tick box like manner.
Be cautious.
On shared hosting:
- You don’t control MySQL configuration
- Large indexing operations can be slow or time out
- Always back up before making changes
Focus areas for WordPress/WooCommerce.
Common wins:
- wp_postmeta.meta_key
- wp_postmeta.post_id
- wp_posts.post_name
- wp_posts.post_type
These are heavily used in:
- product lookups
- variation queries
- metadata joins
How to index database tables, when using a VPS or dedicated server (with root access):
Enable slow query logging.
In MySQL config:
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1This logs queries that take longer than 1 second.
Then in the shell create the slow query log file, and change it’s ownership to MySQL:
touch /var/log/mysql/slow.log
chown mysql:mysql /var/log/mysql/slow.logThen restart MySQL:
systemctl restart mysqldThen tail the slow.log file:
tail -n 200 /var/log/mysql/slow.logOr follow the slow.log file:
tail -f /var/log/mysql/slow.logOr search the slow.log file:
grep search-for-this var/log/mysql/slow.logAnalyse slow queries.
Look for:
- Full table scans
- Joins without indexes
- Queries using LIKE ‘%…%’
Use EXPLAIN.
Run:
EXPLAIN SELECT ...This shows:
- Whether indexes are used
- How many rows are scanned
- Join types
Key red flags:
- type: ALL (full table scan)
- high row counts
Add targeted indexes.
You’ll need to take your site’s database prefix into account, as this might not be wp_ .
Based on real query patterns, add indexes where needed:
ALTER TABLE wp_postmeta ADD INDEX post_id_meta_key (post_id, meta_key(191));Composite indexes (multiple columns) are especially powerful for WooCommerce queries.
Tune MySQL.
With root access you can also:
- Increase buffer pool size
- Optimise query cache (if applicable)
- Tune temporary table sizes
Real world WooCommerce impact.
WooCommerce relies heavily on:
- wp_posts
- wp_postmeta
And frequently performs:
- joins between them
- lookups on meta_key
- filtering on product attributes
Without proper indexing:
- queries become slow
- CPU usage increases
- memory pressure builds
- performance degrades under load
With proper indexing:
- queries are faster
- fewer resources are used
- the site scales much better
Indexing helps your database find data faster by avoiding full table scans. This reduces query time, lowers resource usage, and improves scalability.
On shared hosting you can use tools like Query Monitor and phpMyAdmin to identify and add useful indexes, while on a VPS you can take a more advanced approach using slow query logs and EXPLAIN to optimise performance based on real query behaviour.
Recommended WooCommerce indexes.
Before applying this:
- Always take a database backup first
- Test on staging if possible
- Don’t blindly add indexes to everything (these are targeted, high-value indexes)
postmeta (highest impact table)
This is the most important table to optimise, if you’re not using HPOS.
You’ll need to take your site’s database prefix into account, as this might not be wp_ .
Add index on meta_key:
ALTER TABLE wp_postmeta ADD INDEX meta_key (meta_key(191));Add composite index on post_id + meta_key:
ALTER TABLE wp_postmeta ADD INDEX post_id_meta_key (post_id, meta_key(191));Why this helps:
- WooCommerce heavily queries postmeta by post_id and meta_key
- This improves product, variation, and attribute lookups
posts.
You’ll need to take your site’s database prefix into account, as this might not be wp_ .
Index post_name (slug lookups):
ALTER TABLE wp_posts ADD INDEX post_name (post_name(191));Index post_type:
ALTER TABLE wp_posts ADD INDEX post_type (post_type);Why this helps:
- Used in product queries, URL resolution, and canonical lookups
- Helps when filtering by product type or fetching specific slugs
woocommerce_order_items.
You’ll need to take your site’s database prefix into account, as this might not be wp_ .
ALTER TABLE wp_woocommerce_order_items ADD INDEX order_id (order_id);Why this helps:
Frequently joined when loading orders and order details.
woocommerce_order_itemmeta:
You’ll need to take your site’s database prefix into account, as this might not be wp_ .
ALTER TABLE wp_woocommerce_order_itemmeta ADD INDEX order_item_id (order_item_id);
ALTER TABLE wp_woocommerce_order_itemmeta ADD INDEX meta_key (meta_key(191));Why this helps:
Order metadata is heavily queried when viewing/administering orders.
Optional: terms / taxonomy-related tables.
You’ll need to take your site’s database prefix into account, as this might not be wp_ .
If you use lots of attributes/categories:
ALTER TABLE wp_term_taxonomy ADD INDEX taxonomy (taxonomy);This helps with product category and attribute filtering.
Why these indexes help WooCommerce.
WooCommerce commonly performs queries like:
- Joining wp_posts ↔ wp_postmeta
- Filtering products by attributes stored in postmeta
- Loading variations (each variation = post + meta rows)
- Fetching order data across multiple meta tables
Without indexes:
- These become full table scans
- Queries slow down as data grows
With indexes:
- The database can jump directly to relevant rows
- Joins become much more efficient
- Page loads reduce database overhead
What NOT to do:
- Don’t index every column in wp_postmeta (huge table, high write cost)
- Don’t add redundant indexes with overlapping columns
- Don’t apply indexes without understanding query patterns at scale
How to apply safely on shared hosting:
- Use phpMyAdmin
- Run one query at a time
- Monitor performance after each change
How to apply safely on a VPS:
- Use phpMyAdmin
- Apply via CLI or migration scripts
- Use backups and staging
Table indexing summary.
WooCommerce performance is heavily influenced by how efficiently the database can query wp_posts and wp_postmeta. Adding targeted indexes to frequently queried columns like meta_key, post_id, and post_name allows MySQL to avoid full table scans, significantly reducing query time and database load. This is especially important for stores with many products, variations, and orders.
Frequently Asked Questions
Why is my WooCommerce site slow or showing “Error establishing a database connection”?
This usually happens when your database is under heavy load or cannot respond in time. WooCommerce stores a large amount of data in WordPress database tables such as wp_posts and wp_postmeta (or HPOS tables if enabled). As these tables grow, queries become slower, and under high traffic or inefficient queries, the database server may run out of resources or fail to respond.
What causes WooCommerce databases to become large?
WooCommerce stores products, variations, orders, and metadata in the database. Over time, this data accumulates as your store grows. Additional factors such as transients, plugin data, and historical orders all contribute to increasing database size, which can impact query performance if not managed properly.
What is the difference between a slow website and a slow database?
A slow website can be caused by either rendering issues (frontend performance) or application/database issues (backend performance). If your admin dashboard is slow as well as the frontend, this typically indicates a database or server-level problem rather than a theme or frontend issue.
What is HPOS and how does it help?
High-Performance Order Storage (HPOS) is a WooCommerce feature that moves order data out of the traditional wp_posts and wp_postmeta tables into dedicated WooCommerce tables. This reduces load on core WordPress tables, improves query performance, and helps isolate order data from page content queries.
What are database indexes and why are they important?
Indexes help the database locate data more efficiently without scanning every row in a table. They act like a lookup structure, allowing queries to jump directly to relevant records. Proper indexing can significantly improve performance for large tables, especially when filtering, joining, or sorting data.
Can indexing improve WooCommerce performance?
Yes. WooCommerce relies heavily on database queries involving wp_posts and wp_postmeta. Adding targeted indexes to commonly queried columns can reduce query time, lower CPU usage, and improve scalability. However, indexes should be applied carefully to avoid unnecessary overhead on write operations.
Why do 404 errors impact WooCommerce performance?
When a 404 error occurs, WordPress attempts to find the closest matching content using additional database queries. In large WooCommerce stores, these extra lookups can add overhead. If 404s occur frequently (for example, due to broken links or bot traffic), this can increase database load unnecessarily.
What is the role of object caching in WooCommerce?
Object caching stores the results of database queries in memory so they can be reused without repeated database access. This reduces the number of queries executed per page load and improves performance, particularly for high-traffic WooCommerce sites.
Why do transients affect database performance?
Transients are temporary cached values stored in the database (typically in the options table). Without object caching, these transients are repeatedly accessed on page load. Over time, a large number of transients can contribute to database bloat and increased query overhead.
Should I upgrade my hosting or optimise my database?
Both approaches are often necessary. Increasing server resources (CPU and RAM) can help handle higher loads, but without database optimisation, performance issues may persist. Optimisation helps reduce inefficiencies, while upgrading resources increases capacity.
