[Resolved] Performance with large amount of Data and indexing issue

[Author]

Posts

[peterN-4]

I have just started some tests with one of your testing sites before I contract a developer to develop a directory website.
I am wondering how much data toolset can support with good performance.
1. The plan is to either have one custom post type or about 10 custom post types depending on what toolset can handle with excellent performance.
2. The custom posts will be between 3,500 and 4,000.
3. I will also have many Taxonomies because I need to have each one of the USA states as a taxonomy and within each state, I will have several cities. I will aslo have a few other taxonomies may be 20 maximum. Now the taxonomies for states and cities will take the bulk of the taxonomies. So, I expect on the minimum 300 taxonomies.
4. I will aslo have many custom fields. May be 20-40. I am still working on this.
I am wondering if this is something Toolset can handle. This would be my prefered way of building the website. I consider my site as a niche site. If this is not achievable, I can consider scalling down and try to do several very specific niche sites. What capacities of custom post types, custom posts, taxonomies and custom fields can toolset handle with great speed performance?

My other question is about indexing of custom fields.
In one of the questions asked in the forums, your support team member made reference to an article which is outside of toolset website. In that article, this sentence caught my attention "The value column in the custom field table is not indexed – you are basically doing a search through data that is not intended for that purpose. Even when you add an index to the post meta table, it won't fix it."
This is the article I am making reference to hidden link and this is the post where your support team member made reference to the said article. https://toolset.com/forums/topic/importing-a-large-set-of-data-in-wordpress-using-toolset/

So, can I go by that statement that the "The value column in the custom field table is not indexed" and this also applies to custom field values for content created with toolset. I am asking this because the answer will definately affect how I use custom fields.

Thank you.

[Minesh]

Hello. Thank you for contacting the Toolset support.

Based on the information you shared with your initial reply, even if you try to use only WordPress without Toolset, having such amount of posts, custom fields, taxonomies will have possible impact on performance. Please note that Toolset set is still not involved yet.

Having said that, In general, performance obviously affects the site speed when you have number of custom post type setup with the number of taxonomies as well as custom fields.

In other words, it depends also on your server configuration (memory,cashing) as well as number of other factors such as how complex your data structure is.

The key point for you should have in mind is that querying based upon custom fields or taxonomy is slow, an unavoidable consequence of how WordPress database tables are structured. (This being independent of Toolset, which is just built on the core WP structures.) As Toolset follows the WordPress standard to store custom fields for posts, taxonomies and users.

So retrieving posts where they have a custom field or taxonomy matching some value is slow, but retrieving the value of a custom field for a particular post is very fast.

Taxonomies are optimized for querying in a way that custom fields are not, and so don't have the same limitations (and what limitations there are come from WP not from Toolset).

Regarding your another question about "The value column in the custom field table is not indexed":
==>
That's true, as I already acknowledge that Toolset follows WordPress standard to store custom field. So, for instance custom field value will be saved to postmeta table and where its already mentioned that you can not index the value column and even if you try to index it there will not be any effect.

I suggest you should try to setup a test site and step by step confiture your post types, custom fields and taxonomies and see in action and check what you can do with such big amount of data.

[peterN-4]

Thanks Minesh for for this detailed response.
I understand the best approach is to to keep testing. As I prepare for the testing, the following might be helpful.
1. Do you know by any chance what have the most and the least effect on performance between custom post types, taxonomies, custom posts and custom fields if they are many?
2. Is it possible to provide an example to support this sentence from your reply. "So retrieving posts where they have a custom field or taxonomy matching some value is slow, but retrieving the value of a custom field for a particular post is very fast." The answer will help me to understand better how to structure the website and how to retrieve posts for better performance.
3. I checked a number of websites on the showcase and was really impressd by the performance of one website. I am not sure if I am allowed to post it here, If I am not, please remove the link. This website hidden link I think the performance may have to do with the setup. Now the problem unlike other websites, I could not differentiate between taxonomies, views/archive pages and custom posts. I couldn't even tell if it has custom fields from the following pages.
hidden link
hidden link
I will appreciate your help on understanding these pages and differentiating various items.

If this is outside the scope of support, I wouldn't mind a 10-20 minutes paid consultation session if that kind of arrangement exist.

Thank you very much.

Regards Peter

[Minesh]

1. Do you know by any chance what have the most and the least effect on performance between custom post types, taxonomies, custom posts and custom fields if they are many?
==>
I do not have any concrete example but as shared, even if without Toolset as well, it will have negative impact on performance until and unless you use cache, optimize queries and use other specialized performance tools.

2. Is it possible to provide an example to support this sentence from your reply. "So retrieving posts where they have a custom field or taxonomy matching some value is slow, but retrieving the value of a custom field for a particular post is very fast." The answer will help me to understand better how to structure the website and how to retrieve posts for better performance.
==>
When we say retrieving the value of custom field for a specific post will be fast, you can imagine that we can use the WordPress standard function get_post_meta() to get the value of specific custom field based on post ID. For example:

$book_rating = get_post_meta(111,'wpcf-book-rating',true);

As you can see with the above example, we are getting book rating value stored in database for a post ID 111.

when we say "retrieving posts where they have a custom field or taxonomy matching some value is slow":
- At this moment we will have created a view with custom search that queries the posts type to filter the results with the frontend filter you added. Please check the following Doc about how you can create custom search using Toolset:
- https://toolset.com/course-lesson/creating-a-custom-search/

3. I checked a number of websites on the showcase and was really impressed by the performance of one website. I am not sure if I am allowed to post it here, If I am not, please remove the link. This website hidden link I think the performance may have to do with the setup. Now the problem unlike other websites, I could not differentiate between taxonomies, views/archive pages and custom posts. I couldn't even tell if it has custom fields from the following pages.
hidden link
hidden link
I will appreciate your help on understanding these pages and differentiating various items.

If this is outside the scope of support, I wouldn't mind a 10-20 minutes paid consultation session if that kind of arrangement exist.
===>
We do not offer paid consultation, if you want custom consultation you can always contact our certified partners:
- https://toolset.com/contractors/

Howerver:
- with this link hidden link
Its seems its just displaying the course category (term) names on a page. It's using the view to display the term names.

- with this link hidden link
It's a taxonomy archive where its listing the course belongs to term '2d-animation'. Where with each box it first displays the post name then custom field then it seems displays the post body and then rating, number of students and course type (all those seems to be custom fields).