Massive number of rows in wp_postmeta

We have a relatively large site using Course press pro. Its slow. but we're moving it to a new server. I noticed the size of the wp_postmeta table is huge. 1.6 million rows.
Is this ok? Is this acceptable or is something wrong here?

mysql> select count(*) from wp_postmeta;
+----------+
| count(*) |
+----------+
| 1609533 |
+----------+

  • Predrag Dubajic
    • Support

    Hey Lisa,

    Hope you're doing well today :slight_smile:

    CoursePress has a lot of custom fields and it could cause large number of rows in _postmeta table because of it, especially if you have large number of courses and users.

    You could try optimizing your db a bit with plugins like there:
    https://wordpress.org/plugins/wp-optimize/
    https://wordpress.org/plugins/rvg-optimize-database/

    Just make sure you have backup ready before doing so.

    Best regards,
    Predrag

  • Robert
    • The Incredible Code Injector

    I ran across this article, but it doesn't work for me. :disappointed:

    From: https://www.smarthomebeginner.com/clean-wp-commentmeta-wp-postmeta/
    ---
    Discussion: Cleaning wp_postmeta table
    To clean wp_postmeta table, again click on the database name on the left side and then go the SQL tab. Enter the following SQL query into the textbox, ensure that semi-colon appears as the delimiter and hit “Go”.

    SELECT * FROM wp_postmeta pm LEFT JOIN wp_posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL;
    DELETE pm FROM wp_postmeta pm LEFT JOIN wp_posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL;
    Here too, we first select the postmeta data for posts that do not exist anymore and then we delete them. After execution, you should see the successful execution message and the number of rows that were affected.
    ---
    When I do this, I get: (See screenshot)
    https://www.dropbox.com/s/794r0ana1v4telb/Screenshot%202018-11-15%2017.05.57.png?dl=0

    Any idea how to fix this query?

    Thanks much!

  • Adam Czajczyk
    • Support Gorilla

    Hi Robert

    I don't think this query actually needs fixing, I'd rather say that there's simply no "orphaned" postmeta data there - which is actually a good thing :slight_smile:

    What Predrag suggested, however, is a bit different thing - "revisions" are not "orphaned" postmeta so they need a bit different treatment. It's difficult to give any more detailed diagnose without actually checking the database but did you give those plugins a try?

    Alternatively, there's also an option to clear such things in our Hummingbird too - there's a "Database cleanup" tool built-in:

    https://premium.wpmudev.org/project/wp-hummingbird/

    While Hummingbird could help with site performance as well, you don't necessarily have to enable all its tool right from the start but just use the db cleanup tool :slight_smile:

    If none of that helps/works (just make sure that you do have a full backup of your database before trying any of them, that's extremely important!), let me know and I'll be glad to take a look into the database to see what else I could suggest.

    Best regards,
    Adam

Thank NAME, for their help.

Let NAME know exactly why they deserved these points.

Gift a custom amount of points.