Custom Taxonomies – postmeta or custom tables?

  • kshengelia

    (@kshengelia)


    10 years, 11 months ago

    Hello,

    I am going to run WordPress Multisite which will have one custom post type. This custom post type will have many taxonomies (like 10-15). I will use this custom post type for product and taxonomies for product specification like color, size, product-specific specifications like RAM, CPU for servers, 3G/2G for mobile phones, etc.

    I have done a lot of research on every aspect of my future website and while I’ve solved most of the tasks via plugins and/or custom solutions, it seems that biggest challenge is how WordPress handles databases.

    My priorities are:

    1. Search – end-users should be able to filter search results with product specifications, just like Amazon or any other ecommerce website. I’ve seen WordPress examples of this: Shopp ecommerce plugin supports faceted search, WooThemes have done same with some of their themes (http://demo2.woothemes.com/?name=estate)

    2. Flexibility – this metadata should be flexible. For example, I should be able to show them in statistics in future. Chosen plugin supports statistics like Popular products, etc. but I’d like to provide statistics based on custom taxonomies – product specifications. For example – Top YELLOW Shirts.

    I have read almost everything worthy about custom post types and when one should use custom database tables to store data as well as pros & cons of each decision.

    It seems that Custom Post Types isn’t quite flexible to do some complex things with metadata as they are stored in one table. Every single website said that but I wonder what complex means. Is what I need complex or not?

    As with custom database tables – they say that you should avoid them unless you have no choice. They found it very complex and, at the same time, it’s hard to cache them as they are outside of WordPress territory.

    And about speed & performance – does one of these solutions have better effect on speed & performance than other one? For example, if I go with custom database tables, will that reduce load time when user tries to filter search results? What I have understood here (http://podsframework.org/docs/comparisons/compare-storage-types/) is that it does.

    Please provide some suggestions no matter if they are basic or they answer to my questions exactly. Few sample questions:

    What complexity exactly means in terms of creating custom database tables – is this nearly impossible for WordPress or is it just few more thousand dollars of development costs?

    What will be cons (as I am aware with Pros already like built-in caching, etc.) of using Custom Post Types in scenario described above (filtering, etc.)?

    Some key information: I will have at very maximum 1000 websites in my multisite but quantity of products (custom post types) might be millions.

    Best regards,

    Kote Shengelia

Viewing 6 replies - 1 through 6 (of 6 total)
  • Scott Kingsley Clark

    (@sc0ttkclark)

    10 years, 11 months ago

    The general consensus within the WordPress ecosystem is that custom tables are bad and should be avoided primarily because they operate outside of the normal architecture of WordPress itself. Something like Pods offers a fusion of both, which lets you have custom post types / custom taxonomies, but powered by a separate table for their custom fields. It also integrates with the *_*_meta functions in WordPress, which WP core and other plugins utilize to interact with the fields, so the location being in a table is almost inconsequential.

    The only drawbacks are that WP_Query meta references don’t work, but we’re working on that.

    You could also go the route of Advanced Content Types in Pods, which are completely separate from post types, and live in their own table entirely. You create only the fields you need, etc..

    For searching, I’m not sure the performance is entirely noticeable if you’re on an optimized server / site utilizing object caching etc. Pods caches just like WordPress for it’s data across the entire codebase, so that’s something we’ve thought about deeply.

    I recommend keeping to the standard Custom Post Types and meta-based field storage in most cases, but something you’ll find is quite bad when filtering is that when you run a query in WordPress for 10 fields, that’s 10 extra database SQL joins just for the meta alone, and that can really slow things down when you have a lot of data.

    As you saw in our diagram, sometimes you just can’t beat the performance of a well architected content type table.

    Thread Starter kshengelia

    (@kshengelia)

    10 years, 11 months ago

    Hello Scott,

    Thanks for your reply. It’s quite optimistic for me 🙂

    Let me answer & ask more questions on some parts of your reply.

    The general consensus within the WordPress ecosystem is that custom tables are bad and should be avoided primarily because they operate outside of the normal architecture of WordPress itself.

    Yes that’s what I meant in “being outside of WordPress territory”.

    Something like Pods offers a fusion of both, which lets you have custom post types / custom taxonomies, but powered by a separate table for their custom fields. It also integrates with the *_*_meta functions in WordPress, which WP core and other plugins utilize to interact with the fields, so the location being in a table is almost inconsequential.

    In other words, using Pods will let me have native Custom Post Type functionality AND custom tables. http://wordpress.stackexchange.com/questions/62862/how-to-use-custom-post-type-apis-but-use-a-different-db-table?rq=1 – This is about one-year-old thread about same topic and author of reply says that it’s not possible I think she/he wasn’t aware of Pods at that time or maybe she/he was talking about some other kinds of problems?

    For searching, I’m not sure the performance is entirely noticeable if you’re on an optimized server / site utilizing object caching etc. Pods caches just like WordPress for it’s data across the entire codebase, so that’s something we’ve thought about deeply.

    I have chosen WPEngine as my managed WordPress hosting so I think optimizing server won’t be a problem for me.

    As with Pods, let me tell you that I have discovered this framework today despite that I am planning website project for almost 2 months already and it seems that it’s must-use plugin for my project, isn’t it?

    I recommend keeping to the standard Custom Post Types and meta-based field storage in most cases, but something you’ll find is quite bad when filtering is that when you run a query in WordPress for 10 fields, that’s 10 extra database SQL joins just for the meta alone, and that can really slow things down when you have a lot of data.

    As meta-based field storage causes slowing things down and the solution is custom database tables, does Pods solve this? I mean what you have said above that you can have Custom Post Types as well as custom database tables and the only downside is WP_Query which you are working on. One more thing, I am not developer myself, could you tell me what that means in my scenario? I mean what will WP_Query problems will cause. Is it mostly search-related, or what?

    And the last question here I am going to run WordPress multisite as I have already said above and actually, there will be two search locations:

    1. On specific site – where end-user will be able to search custom post types only from that specific site;
    2. On homepage – which will index everything across the network;

    So, in first case, we are talking about defined quantity of posts. For example, maximum quantity of custom post types our site will have is hardly about 40 000. And we will have only few of these kind of huge sites. Others will have hundreds or few thousands.

    In second case, we will have to index hundreds of thousands of products just like wordpress.com search does.

    So, as quantity of items are slowing things down, it will impact only second case right? I mean, sites, itself won’t have huge quantity of posts (if what you mean in HUGE isn’t few thousands). I think it’s good that WordPress Multisite creates dedicated table for each site, this will help specific sites avoid this slowing down right as search bar on specific site already knows in which table it should search for typed product, so instead of searching in hundreds of thousands products (like it would on single installation of WordPress) it will search in thousands.

    Scott Kingsley Clark

    (@sc0ttkclark)

    10 years, 11 months ago

    Right, Pods can be used for CPT AND Custom Tables for storage of it’s fields. No other plugin offers that, so it’s likely they just didn’t know about Pods or I think at that time Pods 2.0 wasn’t out yet so they may haven’t seen it.

    Great that you’re using WPEngine, you’ve done a good job at giving yourself a running start for performance!

    The only issues I’ve seen arise from WP_Query, continue to be performance when doing multiple field lookups, like 8+ is when it starts to slow things down. If you cache the results, that helps offset the slowdowns, but if it’s a page that isn’t cached then you’ll definitely want to be proactive about leveraging object caching wherever possible. I even submitted a fix for one of the major slow downs here: https://core.trac.wordpress.org/ticket/24093

    For a cool partial page caching function, check out http://pods.io/docs/pods-view/

    WordPress search does not allow that ability to search across multiple sites, are you using another solution for that?

    Thread Starter kshengelia

    (@kshengelia)

    10 years, 11 months ago

    Yes, I depend on WPEngine very much as the project is complex and I am more than happy that I can outsource things like performance, security and scalability.

    As with WP_Query, I think, 8 fields will be enough for filtering in most cases. I think even 4-5 fields will be enough. So let me rephrase the question – things are going slow after 8 fields exactly or each field causes slowing down? I mean filtering 1 field is faster than filtering 2 OR filtering 1,2,3,4,5,6,7,8 fields require same time? In other words, does WP_Query have problems exactly after 8 fields (cause that’s how it’s configured right now) OR it’s speed problem even when you are filtering 2 fields BUT it isn’t noticeable?

    WordPress search does not allow that ability to search across multiple sites, are you using another solution for that?

    I am going to run WordPress Multisite, not single installation and while it doesn’t offer such functionality by default, it’s relatively easy to achieve that with custom coding (actually there are pretty good plugins out there which we are going to use). Few examples are WordPress.com search system which searches in millions of blogs and Edublogs.org which let’s users get search results from almost 2 million sites (their sites of course, they are running Multisite).

    Scott Kingsley Clark

    (@sc0ttkclark)

    10 years, 11 months ago

    It’s not an 8 field thing, that was just me saying something in general. I’ve seen issues with certain queries running when multiple fields are referenced, or the same field multiple times, etc.. It’s primarily a MySQL speed problem based on the query the WP_Query generates.

    Thread Starter kshengelia

    (@kshengelia)

    10 years, 11 months ago

    Ok, thank you for information.

    Anyway, I am testing Pods on WPEngine trial installation and I am really excited with it already. Not only it handles custom post types & taxonomies & fields, but how it handles custom settings pages/items will save me hours of work.

    Thank you!

Viewing 6 replies - 1 through 6 (of 6 total)
  • The topic ‘Custom Taxonomies – postmeta or custom tables?’ is closed to new replies.