Having issues with wp_postmeta and wp_usermeta tables too big

Modern MariaDB and MySQL versions allow your tables’ keys to be more efficient. Better keys can help a lot to speed up the kinds of database queries WooCommerce (and core WordPress) use.

If you have a recent MySQL version that can handle the DYNAMIC row format, these postmeta keys will help a lot.

    PRIMARY KEY (`post_id`, `meta_key`, `meta_id`),
    UNIQUE KEY `meta_id` (`meta_id`),
    KEY `meta_key` (`meta_key`, `meta_value`(32), `post_id`, `meta_id`),
    KEY `meta_value` (`meta_value`(32), `meta_id`)

This plugin installs those database keys for you. To avoid timeouts when installing the keys, you should run it with wp-cli.

This plugin mitigates some database inefficiencies with many users.

Related Posts:

  1. What is this JavaScript “require”?
  2. How to resolve ORA-011033: ORACLE initialization or shutdown in progress
  3. Oracle SQL query for Date format
  4. Failed to connect to mysql at 127.0.0.1:3306 with user root access denied for user ‘root’@’localhost'(using password:YES)
  5. Can I have multiple primary keys in a single table?
  6. Using wpdb to connect to a separate database
  7. How can I make a WordPress database portable and url independent?
  8. wpdb->insert multiple record at once
  9. How to migrate a HTTPS WordPress installation to localhost?
  10. Database connection close
  11. How to stop WordPress using utf8mb4_unicode_520_ci collation?
  12. How to create bulk page and content? [closed]
  13. Importing posts from old website to new conflicting post ID’s?
  14. Exporting revisions
  15. WP_Options ID high
  16. Default WordPress settings API data sanitization
  17. Fatal error: Call to undefined function dbDelta()
  18. WordPress for a very large website
  19. How to use multiple database in wordpress?
  20. Is removing orphaned wp_postmeta records safe?
  21. How to intercept update_option() before it saves data?
  22. wpdb query on custom table not working [closed]
  23. Using table lock with wp_insert_post?
  24. Should I remove transients from the wp_options table?
  25. WordPress doesn’t show all posts in database
  26. Adding new row to wp_post table
  27. Extracting the post_id via the wp_insert_post action (external db query)
  28. How to interact with another wordpress install on same database?
  29. Are custom tables in the database overwritten when WordPress is updated to new versions?
  30. What is the proper way to validate and sanitize JSON response from REST API?
  31. How to migrate a website based on a custom database to wordpress?
  32. How do I get the posts within a certain year/date(with sql query)?
  33. batch UPDATE autoload value in wp_options table
  34. Displaying BLOB PDF File
  35. Saving into post_excerpt or post_content
  36. Maintain user profile data in a non-WordPress database
  37. How to fix the disappearance of the “\” character when updating a post in the database
  38. Data storage for JS app using WP REST API
  39. WP CLI Core install – blank database
  40. Update from 4.5 to 4.6 failing
  41. Setup 3 Sites To Connect To 1 Database and Share Data
  42. How can I push local database changes to production, without impacting recent posts/pages that were published on production server?
  43. Setup private content for specific users with daily updates [closed]
  44. Does auto_load to ‘no’ in wp_options improve performance
  45. Call to undefined function get_user_meta() – trying to access data in MySQL from custom fields
  46. Have working sql query… trying to adjust it to use $wpdb
  47. Best host for running large mem and processor intensive WordPress sites?
  48. Will there be any concern if I upgrade my current WordPress Database server from Centos 7 Mariadb 5.5 to Centos 8 Mariadb 10.3?
  49. In which format the data is stored in WordPress Databases?
  50. Is it possible to rebuild the website while not accesseing the original database?
  51. Error message on blog posts only on my local environment [closed]
  52. how to read database fields
  53. $wpdb->query can’t insert data
  54. WSOD 3.9.1 – Tried Everything so far
  55. Can you refresh ONLY the wordpress adminbar and not the whole page?
  56. After database migration, theme mods don’t show [closed]
  57. Sending posts from Python to WordPress
  58. Problem migrating to localhost
  59. Remote database connection and local development
  60. How can I detect right db type?
  61. Add search bar to Custom Database Table in WordPress Dashboard
  62. Design problems when exporting remote database for local development
  63. How do you render posts from outside wordpress?
  64. Website displays old version of page
  65. A change in URL slug in database returns 404, how can I do auto-redirect?
  66. Second ezSQL initialization for MSSQL
  67. Displaying table data on a page
  68. muliple wordpress installs (network) on different databases
  69. Database structure cheatsheet
  70. White Screen in Home Page
  71. Continuous SQL query errors from outside source
  72. How to escape percentage sign(%) in sql query with $wpdb->prepare?
  73. Whats the best practise on how to store json data from a custom settings page?
  74. Recovery – Restore Database after moving folder location locally
  75. Migrate Users From laravel to wordpress
  76. WordPress has been updated! Before we send you on your way, we have to update your database to the newest version
  77. /wp-admin not accessible after migrating to local host (no plugin issue)
  78. Job and Employee Performance Tracking with Product Safety
  79. how add more field to wp user and save it to database
  80. set_transient fails if the value has more than 60.000 characters
  81. How do I cleanup my database with data that is no longer being used?
  82. Malware in old website – how to migrate?
  83. Set SQL_BIG_SELECTS and MAX_JOIN_SIZE on a WP_Query
  84. How to optimize wp_option table?
  85. How to display $wpdb->insert_id; value on redirected page
  86. Database for JQuery Autocomplete field
  87. Should I Use only wpdb Class to Write Custom Queries?
  88. Auto populate form fields based on serial input or pull listings from other websites?
  89. Create Tables in WordPress
  90. Catchable fatal error in formatting.php
  91. Wrong url for the images while using same database for two WordPress installations
  92. WordPress Failed to Login (DB Error)
  93. Sending WordPress database information to cross domain the safe way?
  94. Database Name Change
  95. Weird characters in title if it contain “ ”
  96. Where To Find bb_profile_data(); In bbPress?
  97. ERROR: “Table Prefix” must not be empty
  98. How do you build a database-centric site in WP?
  99. One information repeated on multiple locations on one site [closed]
  100. Attach posts based on category and position