**TL;DR — wp_wc_product_attributes_lookup is a denormalized index table WooCommerce uses to make filtered shop pages fast.** On big catalogs (10K+ products with multiple attributes) it gets enormous and WooCommerce will helpfully regenerate it in the background after almost any product change, which can hammer your database for hours. Fix: pause the regeneration during business hours, throttle the batch size, and consider disabling the lookup table entirely if you don't actually use the layered nav filters it powers.
The first time I encountered this one I had no idea what it was. A WooCommerce client called about general site sluggishness — page loads were 4-6 seconds, the admin was dragging, and overall throughput was bad. The Query Monitor plugin showed slow queries, but the slow queries weren't coming from the storefront templates I expected. They were coming from a table I'd never paid attention to: `wp_wc_product_attributes_lookup`.
The table had 11 million rows. The store had 38,000 products. That ratio is what you should remember.
What is this table even for?
WooCommerce introduced `wp_wc_product_attributes_lookup` in version 4.6 to speed up the layered navigation filters on shop pages — the "filter by color, size, brand" widgets. Before this table existed, those filters had to JOIN through `wp_postmeta` and `wp_term_relationships` and generally torture the database to figure out which products matched a given combination of attributes. With the lookup table, WooCommerce maintains a denormalized row for every product-attribute-term combination, so filtering becomes a single indexed lookup.
The math gets ugly fast. A product with 5 attributes and 3 values per attribute generates `5 × 3 = 15` rows in the lookup table. A variable product with 4 attributes and 8 variants per attribute generates `4 × 8 = 32` rows. Multiply that by 38,000 products with mixed configurations and you can easily land on 10-15 million rows. The table itself isn't the problem. The problem is how WooCommerce treats it.
Why WooCommerce keeps regenerating it
WooCommerce regenerates rows in this table whenever something might have invalidated them. "Something might have invalidated them" turns out to be a very broad definition. Adding a product. Editing an attribute. Bulk-importing 500 SKUs from a CSV. Running a stock sync. Saving a product through the REST API. Even some plugin updates trigger a full regeneration.
And "regeneration" doesn't mean "recalculate the rows that changed." It means "truncate the affected rows and rebuild them from scratch via Action Scheduler." Which means thousands of `INSERT` statements, queued up, running in the background, hitting the database as fast as Action Scheduler will let them.
On a small store this is invisible. On a 38,000-SKU store it can take three or four hours and saturate the database the entire time.
Confirming it's the lookup table
Run `SHOW PROCESSLIST` while the site is slow:
SHOW PROCESSLIST;On the affected client, this is what I saw (trimmed):
Id Command Time State Info
2841 Query 47 Sending data INSERT INTO wp_wc_product_attributes_lookup ...
2842 Query 44 Sending data INSERT INTO wp_wc_product_attributes_lookup ...
2843 Query 41 Sending data INSERT INTO wp_wc_product_attributes_lookup ...
2851 Query 8 Sending data SELECT * FROM wp_wc_product_attributes_lookup WHERE ...Three concurrent inserts running for 40+ seconds each, plus a SELECT from the same table waiting in line behind them. The inserts were coming from `WC_Product_Attributes_Lookup_Data_Store::create_data_for_product`. That's the regeneration job.
I also ran EXPLAIN on the SELECT to confirm there wasn't a missing index making things worse:
EXPLAIN SELECT product_id FROM wp_wc_product_attributes_lookup
WHERE term_id = 1247 AND in_stock = 1
ORDER BY product_id LIMIT 16;It used the index correctly and only touched ~3,400 rows. The schema was fine. The pure INSERT-flood from the regeneration job was what was breaking things — table-level contention from Action Scheduler hammering the same table from multiple workers.
Pause the regeneration during business hours
WooCommerce exposes a setting for this, but it's hidden under WooCommerce → Status → Tools. The button is called "Regenerate the product attributes lookup table." That's the manual trigger. The automatic regeneration runs whenever the data store decides something needs rebuilding, and you can pause that with a transient:
// Drop into a mu-plugin.
add_action('init', function() {
if (!function_exists('wc_get_container')) return;
$now = (int) date('G');
if ($now >= 8 && $now <= 22) {
update_option('woocommerce_attribute_lookup_enabled', 'no');
} else {
update_option('woocommerce_attribute_lookup_enabled', 'yes');
}
});This disables the lookup table from 8am to 10pm and re-enables it overnight. WooCommerce gracefully falls back to the slower postmeta-based queries during the day (which is fine for an audience that mostly browses by category, not attribute filters) and runs the heavy regeneration when nobody's around. Crude, but it works.
Throttle the batch size
If you can't disable the lookup table at all because your storefront genuinely depends on attribute filters, the next move is making the regeneration less aggressive. WooCommerce has a filter for this:
add_filter('woocommerce_attribute_lookup_regeneration_step_size', function() {
return 5;
});Default is 100 products per batch. Setting it to 5 means each Action Scheduler run only rebuilds 5 products at a time, which dramatically reduces table-level contention. The regeneration takes longer overall — sometimes 12+ hours instead of 3 — but the database stays responsive while it runs.
Don't drop the batch size to 1 thinking smaller is always better. There's per-batch overhead in Action Scheduler (reading the queue, claiming the action, marking it complete). At batch size 1 you spend most of your time on overhead and almost none on actual work. Somewhere between 3 and 10 is usually the sweet spot.
Do you actually need this table?
This is the question I always ask before touching anything. If your shop pages don't use the layered navigation filters at all — say, your customers always come in via category links or product search — then the lookup table is doing zero useful work for you. WooCommerce builds it dutifully, regenerates it constantly, and your visitors never benefit from it once. In that case, the right answer is to disable it permanently:
update_option('woocommerce_attribute_lookup_enabled', 'no');I've done this on at least four big stores and nobody noticed any storefront slowdown. The table sits there, empty and idle, and the database load drops by 30-60% depending on how often product imports were running.
The actual lesson here
WooCommerce is an immensely capable platform, but it makes a lot of decisions on your behalf about what background work needs to happen, and those decisions don't scale linearly. A feature that's invisible at 500 SKUs becomes a daily incident at 50,000. Anything you build on top of WordPress and WooCommerce at large scale will eventually hit some version of this, and the fix is almost always the same shape: figure out what the platform is doing for you, decide whether you actually need it, and either turn it off or throttle it.
