This plugin vs custom table – question regarding performance

  • Resolved wittywolk

    (@wittywolk)


    7 months, 2 weeks ago

    Hi,
    I run a ‘Find a lawyer’ type of website made with Bricks, ACF Pro and WP GridBuilder for filtering.
    I have around 15,000 lawyers (posts) – each has it’s own page, and there is a global page where user can filter lawyers based on city, law specialization, etc. (taxonomies) with WP GridBuilder facets.

    It’s working okay-ish, but I’m looking for a ways of improving it. I’m not much of a backend guy, and I found a couple of competition plugins for ACF that store it’s data in custom tables instead of a long wp_postmeta – Pods, or MetaBox (https://metabox.io/move-custom-fields-data-to-custom-tables/) towards which I’m leaning into.

    But if it’ll be in a custom table, then I guess your plugin won’t optimize it’s queries.

    So my question is… Do you have any idea which route would be better? Default storing of data without custom tables & your plugin, or custom tables? Is there a way for me to measure the effects of both approaches that you’re aware of?

    Any help would be appreciated, thanks!

Viewing 3 replies - 1 through 3 (of 3 total)
  • Phil

    (@probablynotphil)

    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.

    Plugin Author OllieJones

    (@olliejones)

    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.

    Thread Starter wittywolk

    (@wittywolk)

    @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.


Viewing 3 replies - 1 through 3 (of 3 total)
  • The topic ‘This plugin vs custom table – question regarding performance’ is closed to new replies.