How do I purge wp_termmeta (250K+ rows)?

  • Resolved astridix

    (@astridix)


    3 years, 8 months ago

    [No link to my site, it’s currently internal]

    Hi there,

    I’ve been working on the site for a year. Later and later the backup and restore time became longer and longer. The file sizes became larger as well. I had to extend backup and restore timeouts longer and longer.

    The issue is with the wp_termmeta table. It has 250K+ records in it.

    How can I purge it? I need to know which records can be kept or deleted.

    The site uses some 30 WooCommerce product attributes. The attribute names and values have changed over the year. Products have repeatedly imported (some 1,000 records an import, totaling maybe 12,000 records over the year).

    I also noticed that each product import (same exact products from the same .csv file) increases wp_termmeta table size by some 10K records.

    Many Thanks, Caesar.

    WooCommerce Status:

    
### WordPress Environment ###

WC Version: 5.1.0
REST API Version: ? 5.1.0
WC Blocks Version: ? 4.4.3
Action Scheduler Version: ? 3.1.6
WC Admin Version: ? 2.0.2
Log Directory Writable: ?
WP Version: 5.7
WP Multisite: –
WP Memory Limit: 1 GB
WP Debug Mode: –
WP Cron: ?
Language: en_US
External object cache: –

### Server Environment ###

Server Info: Apache/2.4.46 (Unix) PHP/7.4.16 OpenSSL/1.1.1j
PHP Version: 7.4.16
PHP Post Max Size: 2 GB
PHP Time Limit: 7200
PHP Max Input Vars: 1000
cURL Version: 7.75.0
(SecureTransport) OpenSSL/1.1.1j

SUHOSIN Installed: –
MySQL Version: 8.0.23
Max Upload Size: 2 GB
Default Timezone is UTC: ?
fsockopen/cURL: ?
SoapClient: ?
DOMDocument: ?
GZip: ?
Multibyte String: ?
Remote Post: ?
Remote Get: ?

### Database ###

WC Database Version: 5.1.0
WC Database Prefix: wp_
Total Database Size: 176.22MB
Database Data Size: 138.30MB
Database Index Size: 37.92MB
wp_woocommerce_sessions: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wp_woocommerce_api_keys: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wp_woocommerce_attribute_taxonomies: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wp_woocommerce_downloadable_product_permissions: Data: 0.02MB + Index: 0.06MB + Engine InnoDB
wp_woocommerce_order_items: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wp_woocommerce_order_itemmeta: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wp_woocommerce_tax_rates: Data: 0.02MB + Index: 0.06MB + Engine InnoDB
wp_woocommerce_tax_rate_locations: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wp_woocommerce_shipping_zones: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wp_woocommerce_shipping_zone_locations: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wp_woocommerce_shipping_zone_methods: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wp_woocommerce_payment_tokens: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wp_woocommerce_payment_tokenmeta: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wp_woocommerce_log: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wp_actionscheduler_actions: Data: 0.06MB + Index: 0.11MB + Engine InnoDB
wp_actionscheduler_claims: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wp_actionscheduler_groups: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wp_actionscheduler_logs: Data: 0.06MB + Index: 0.03MB + Engine InnoDB
wp_berocket_termmeta: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wp_cartflows_ca_cart_abandonment: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wp_cartflows_ca_email_history: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wp_cartflows_ca_email_templates: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wp_cartflows_ca_email_templates_meta: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wp_commentmeta: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wp_comments: Data: 0.02MB + Index: 0.09MB + Engine InnoDB
wp_csshero4: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wp_jet_post_types: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wp_jet_smart_filters_indexer: Data: 2.52MB + Index: 0.00MB + Engine InnoDB
wp_jet_taxonomies: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wp_jet_widgets_styles: Data: 0.08MB + Index: 0.00MB + Engine InnoDB
wp_links: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wp_ms_snippets: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wp_options: Data: 26.52MB + Index: 0.53MB + Engine InnoDB
wp_pmxi_files: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wp_pmxi_hash: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wp_pmxi_history: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wp_pmxi_images: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wp_pmxi_imports: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wp_pmxi_posts: Data: 0.08MB + Index: 0.00MB + Engine InnoDB
wp_pmxi_templates: Data: 0.17MB + Index: 0.00MB + Engine InnoDB
wp_postmeta: Data: 31.36MB + Index: 3.77MB + Engine InnoDB
wp_posts: Data: 51.50MB + Index: 0.84MB + Engine InnoDB
wp_revslider_css: Data: 0.13MB + Index: 0.00MB + Engine InnoDB
wp_revslider_css_bkp: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wp_revslider_layer_animations: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wp_revslider_layer_animations_bkp: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wp_revslider_navigations: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wp_revslider_navigations_bkp: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wp_revslider_sliders: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wp_revslider_sliders_bkp: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wp_revslider_slides: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wp_revslider_slides_bkp: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wp_revslider_static_slides: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wp_revslider_static_slides_bkp: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wp_snippets: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wp_term_relationships: Data: 3.52MB + Index: 2.52MB + Engine InnoDB
wp_term_taxonomy: Data: 0.41MB + Index: 0.45MB + Engine InnoDB
wp_termmeta: Data: 14.52MB + Index: 16.06MB + Engine InnoDB
wp_terms: Data: 0.44MB + Index: 0.50MB + Engine InnoDB
wp_usermeta: Data: 0.05MB + Index: 0.03MB + Engine InnoDB
wp_users: Data: 0.02MB + Index: 0.05MB + Engine InnoDB
wp_wc_admin_note_actions: Data: 0.16MB + Index: 0.05MB + Engine InnoDB
wp_wc_admin_notes: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wp_wc_category_lookup: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wp_wc_customer_lookup: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wp_wc_download_log: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wp_wc_order_coupon_lookup: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wp_wc_order_product_lookup: Data: 0.02MB + Index: 0.06MB + Engine InnoDB
wp_wc_order_stats: Data: 0.02MB + Index: 0.05MB + Engine InnoDB
wp_wc_order_tax_lookup: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wp_wc_product_meta_lookup: Data: 5.52MB + Index: 12.09MB + Engine InnoDB
wp_wc_reserved_stock: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wp_wc_tax_rate_classes: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wp_wc_webhooks: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wp_wpforms_tasks_meta: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wp_wpvivid_scan_result: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wp_wpvivid_unused_uploads_files: Data: 0.02MB + Index: 0.00MB + Engine InnoDB

### Post Type Counts ###

astra_adv_header: 1
astra-advanced-hook: 2
attachment: 154
br_filters_group: 1
br_product_filter: 2
bsf-sidebar: 1
cartflows_flow: 2
cartflows_step: 4
custom_css: 2
customize_changeset: 2
elementor_library: 21
elementor-hf: 1
elementskit_content: 1
jet-menu: 24
jet-popup: 1
jet-smart-filters: 15
jet-theme-core: 2
jet-woo-builder: 15
nav_menu_item: 27
page: 17
post: 1
product: 778
revision: 5184
shop_coupon: 1
shop_order: 3

### Security ###

Secure connection (HTTPS): ?
Hide errors from visitors: ?

### Active Plugins (28) ###

Akismet Anti-Spam: by Automattic – 4.1.9
Astra Pro: by Brainstorm Force – 3.1.0
Astra Widgets: by Brainstorm Force – 1.2.8
Code Snippets: by Code Snippets Pro – 2.14.1
CSS Hero: by The CSS Hero Team – 4.20
Duplicate Page: by mndpsingh287 – 4.3
Dynamic Content for Elementor: by Dynamic.ooo – 1.13.0
Elementor Pro: by Elementor.com – 3.1.1
Elementor: by Elementor.com – 3.1.4
Fake Pay For WooCommerce: by Anthony Graddy – 1.0.1
Elementor - Header, Footer & Blocks: by Brainstorm Force
Nikhil Chavan – 1.5.7

JetBlocks For Elementor: by Crocoblock – 1.2.8
JetElements For Elementor: by Crocoblock – 2.5.5
JetEngine: by Crocoblock – 2.7.3
JetMenu: by Crocoblock – 2.0.9
JetSearch For Elementor: by Crocoblock – 2.1.9
JetSmartFilters: by Crocoblock – 1.8.3
JetStyleManager: by Crocoblock – 1.1.4
JetThemeCore: by Crocoblock – 1.2.1
JetWooBuilder For Elementor: by Crocoblock – 1.7.9
Jetpack by WordPress.com: by Automattic – 9.5
Advanced Order Export For WooCommerce: by AlgolPlus – 3.1.8
WooCommerce PayPal Checkout Gateway: by WooCommerce – 2.1.1
WP All Import - WooCommerce Add-On: by Soflyy – 1.4.7
WooCommerce: by Automattic – 5.1.0
WP All Import: by Soflyy – 3.5.6
WPForms Lite: by WPForms – 1.6.5
WPvivid Backup Plugin: by WPvivid Team – 0.9.50

### Inactive Plugins (4) ###

Order / Coupon / Subscription Export Import Plugin for WooCommerce (BASIC): by WebToffee – 1.7.3
Sweet Custom Menu: by Caesar Samsi – 0.1
WooCommerce Shipping & Tax: by Automattic – 1.25.8
WooCommerce Stripe Gateway: by WooCommerce – 4.9.0

### Settings ###

API Enabled: –
Force SSL: –
Currency: CAD ($)
Currency Position: left
Thousand Separator: ,
Decimal Separator: .
Number of Decimals: 2
Taxonomies: Product Types: external (external)
grouped (grouped)
simple (simple)
variable (variable)

Taxonomies: Product Visibility: exclude-from-catalog (exclude-from-catalog)
exclude-from-search (exclude-from-search)
featured (featured)
outofstock (outofstock)
rated-1 (rated-1)
rated-2 (rated-2)
rated-3 (rated-3)
rated-4 (rated-4)
rated-5 (rated-5)

Connected to WooCommerce.com: –

### WC Pages ###

Shop base: #38 - /
Cart: ? Page does not contain the [woocommerce_cart] shortcode or the woocommerce/cart block.
Checkout: #40 - /checkout/
My account: #41 - /account/
Terms and conditions: ? Page not set

### Theme ###

Name: Astra
Version: 3.1.1 (update to version 3.1.2 is available)
Author URL: https://wpastra.com/about/
Child Theme: ? – If you are modifying WooCommerce on a parent theme that you did not build personally we recommend using a child theme. See: How to create a child theme
WooCommerce Support: ?

### Templates ###

Overrides: –

### Action Scheduler ###

Complete: 98
Oldest: 2021-02-11 19:40:43 -0800
Newest: 2021-03-14 13:08:45 -0700

Pending: 2
Oldest: 2021-03-14 14:23:56 -0700
Newest: 2021-03-14 19:48:50 -0700

### Status report information ###

Generated at: 2021-03-14 13:14:08 -07:00
Viewing 2 replies - 1 through 2 (of 2 total)

  • Hey @astridix,

    Yikes! That does not sound good. I suspect something has not been going correctly with these imports leading to an explosion of this table.

    Unfortunately, this is a core WordPress table and not a WooCommerce specific one. Changes here can affect other areas of the site.

    I would suggest working with a developer to help you clean this up. You may be able to export the content, delete this data and then import it again. You’d want to make sure the imports are working correctly so this doesn’t happen again.

    This is a fairly complex development topic. I’m going to leave it open for a bit to see if anyone is able to chime in to help you out.

    I can also recommend the following places for more development-oriented questions:

    Plugin Support fevered – a11n

    (@fevered)

    Hey there, since we haven’t heard back from you in awhile I’ll mark this thread as resolved now. If you have any further questions, I recommend creating a new thread.

Viewing 2 replies - 1 through 2 (of 2 total)
  • The topic ‘How do I purge wp_termmeta (250K+ rows)?’ is closed to new replies.