216 - 'D' is for Database

‘A-Z of WordPress’ with Nathan Wrigley and David Waumsley

It’s the 4th in our series of chats called ‘the A-Z of WordPress’ where we attempt to cover all the major aspects of building and maintaining sites with WordPress.

Today, we are really going set pulses racing with the letter ‘D’ for Database!

Use these show notes to follow along with the podcast audio.

Preamble

The word alone sounds incredibly dull, and for years I pretended it did not exist. Even now I do anything to avoid directly interacting with it.

Are they running too many unnecessary and inefficient queries?

Do they clean themselves up after deletion?

Do I want a WordPress solution that may affect performance when there are 3rd party plugins adding to the database.

One good things to come out of GDPR is that a lot of plugin have to provide way to automatically clear up data.

Other points

• There’s been a move recently for plugins to create their own table and not have all in options. Such as… https://yoast.com/yoast-seo-14-0/
• If you use a tool like MainWP or ManageWP, are you even aware that the database structure might have been altered? The feedback for this usually comes as a popup in the WordPress UI.
• What about stored encrypted passwords? How safe is the data that’s being held in the database? Are you keeping data that you don’t need which could be a potential liability if the database is compromised? See… https://stackoverflow.com/questions/31343155/how-to-decode-encrypted-wordpress-admin-password).
• Do you change the wp prefix for security? Is there even any point to doing this?
• How many are too many queries? Two is worse than one and three is worse than two (I could go on!), but what’s the upper limit that you should aim for?
  • “The number of queries (for example, “27Q”) will give you an idea of whether you are having MySQL problems. The number of queries should ideally be under 50. You may start to see slowdowns if the number is above 75. If it is above 150, you have an issue with your theme and/or plugins on that specific page which should be addressed.” – source here.
  • WordPress runs about 23 to start with!
• Recently I contacted ShortPixel about adding 70+ backend queries and 50+ front end ones. Apparently, this is not an issue, as it’s restricted to certain users only when logged in. The actual speed is barely affected.
• The cylindrical icon you get for databases is so far removed from what it really is!!!
• Is having your database hosted elsewhere a better set up than having all-the-things in one place?

What are WordPress’ requirements ?

MySQL version 5.6 or greater OR MariaDB version 10.1 or greater.

Maria being faster, but newer and less used so certainty of support is perhaps a question. https://www.guru99.com/mariadb-vs-mysql

For no good reason at all, here’s a list of the standard WordPress tables:

• wp_commentmeta
• wp_comments
• wp_links
• wp_options
• wp_postmeta
• wp_posts
• wp_terms
• wp_term_relationships
• wp_term_taxonomy
• wp_usermeta
• wp_users

Would the ideal be that you could move easily from one to the other (I think Cloudways allows one click transfers?). I have moved sites from MariaDB to MySQL with no real issues – only a couple of disconnected images.

Tools to help with database management:

Tried:

WP phpMyAdmin

Advanced Database Cleaner

MainWP Maintenance extension

WP Rocket (also does a clean up)

WP Reset

https://wordpress.org/plugins/wp-dbmanager

https://wordpress.org/plugins/wp-optimize

https://wordpress.org/plugins/wp-sweep

https://wordpress.org/plugins/better-search-replace

Never tried:

https://wordpress.org/plugins/wp-clean-up-optimizer

https://codecanyon.net/item/wp-cleaner-pro/21459036https://codecanyon.net/item/smart-cleanup-tools/3714047

When building sites:

https://wordpress.org/plugins/query-monitor (helps find duplicates and slow queries)

https://wordpress.org/plugins/usagedd (simple, shows speed/number of queries when in a Page Builder editor)

Conclusions:

It’s likely that you now know less about databases than you did before you came to this page. That’s a measure our understanding of databases. They’re weird things and, honestly, most of it is Voodoo.

Unless you know what you’re doing, stay away – there be monsters!

Let’s hope that when we get to the next letter… ‘E’, we know a little more. Hope is a good thing, if not always well placed!