This plugin vs custom table – question regarding performance

If you’re not too confident in managing custom database tables, I’d stick with using the standard WordPress postmeta and optimize the keys with this plugin. It will simplify debugging/management of the data.

However it depends on how many different types of meta you’re storing for each post/lawyer. For example, if you’re storing more than ~10 meta fields, you’d probably want to consider switching to a custom table structure if you get to >50k posts/lawyers. It also really depends on how good your hosting is too.

Just my 2 cents though! If Ollie (plugin author) has any thoughts they will be much more qualified than mine.

Good question, thanks!

tl;dr Correct, this plugin doesn’t work with custom tables. Query performance of those tables depends on the design of their keys. That design was dreamed up by the developer of the plugin you’re thinking of using.

This plugin exists because WordPress’s database table design is burdened with the need to handle legacy versions of MariaDb and MySQL. (They weren’t legacy versions, of course, when WordPress got started almost a quarter century ago.) Those versions needed to use so-called prefix keys. The columns holding custom field names were defined to contain 256 Unicode utf8mb4 characters. Those characters take up to four bytes each, but the legacy versions only allow 768 bytes in a column’s key. (Long and relevant story about WordPress’s April 2015 transition from utf8mb3 to utf8mb4 characters omitted.)

So, this plugin determines whether a legacy version is in use. If the version is modern, it changes the keys to get rid of the prefixes and exploit modern keying capabilities, including a sweet sweet thing called a clustered index.

The question for the other plugin developer is, “did you design your tables to avoid prefix keys?” If so, that is good.

But here’s the thing. Core WordPress contains a mess of optimizations, like persistent caching, to support their current structures. Custom tables can’t take advantage of all that work. A plugin handling custom tables must do its own optimizations.

@probablynotphil thanks for the rapid reply! Currently it’s about 15,000 lawyers/posts with each having ~22 meta fields (some are repeatable, a couple of them are taxonomies). Thanks for your input, I guess I’ll try to measure it somehow – with custom table, and without.

@olliejones thanks for detailed response! I’ll try to contact MetaBox about their table design, hope they’ll also shine some light on this dilemma. I asked them previously about similar topic but they said that the default storing vs custom table doesn’t change much when using WP GridBuilder because it creates it’s own tables with data to filter through – I’m not sure about it, but there is no way for you to know that anyway so I’ll stop here. ??

btw thank you for your input regarding ‘SQL_CALC_FOUND_ROWS’ – I think it’ll also help me gain some performance.