How many categories can WordPress handle before performance suffers?
It depends.. you’re not going to find a precise answer, because there are too many variables, ranging from where you host, the kinds of queries you run, the hardware you run it on, your performance expectations, caching, the functions you use, etc
I need approximately 2,750,000 unique categories, with hundreds of thousands of parents.
Sure a database can hold this, but that’s not really what you’re asking
I’m going to test it later, but I’m at work.
This is where you enter the gray area. Imagine you have a warehouse, with 2.5m unique apples, and each one has a label.
First, we’ve already ticked the box about storage, we have the warehouse and it’s storing the apples, all good.
Second, we want the 1,768,923’rd apple.
- How many workers are available to fetch the apple?
- Are the workers old weary people on the verge of retiring, or young athletes fresh from college in the physical prime of their life?
Now, that’s a basic fetch, but are you going to be doing a basic fetch? It’s all good being able to retrieve the apple, but you might want to do something more complex:
- Fetch all red Apples
- Fetch all green Apples
These now encounter new constraints. Lets say there are 700k green Apples, well, you got to haul all those apples, and that takes time. Just as your database has to send all those categories. Page generation times have a time limit, and you only have so much RAM to store things in.
This also leads us to one of the more horrifying types of queries. Imagine if you asked for all big Apples that aren’t green! Now not only do you have to haul all Apples, but you have to individually check each and every Apple, haul it to a new Warehouse of Apples to store all the Apples that aren’t green, then, in the new warehouse search again for just the big ones.
Finally, lets assume your server is lucky and can do this, but you have more than 1 request? How much traffic are you expecting? How many concurrent visitors are you expecting 2? 5? 100? 1000? I’ve seen simpler queries on smaller data sets make servers fall over at high traffic because they wrote their queries to ask for all posts with a post meta that is equal to X. The kinds of queries you use matter.
For example, I expect your site to fall over if you every try to add brackets with the number of posts in the category, or if you ever try to order terms by the number of posts they have. This is because it has to do a subquery for each term to figure that out, and assemble them into a temporary table, then run the query you wanted once it’s ordered as desired.
Databases do a lot of indexing, cataloguing and cheat sheeting to make things fast, and avoid checking each row 1 by 1, and as your table size grows, so do the cost of the more esoteric queries. That’s why you can get some sites are super fast, but slow to a crawl past 200 posts, and other whizz by at 2000 without any slow down
Does anyone know if a basic hosting plan can handle this?
This isn’t a discussion forum, and recommendations of services or products is off topic here. But keep in mind you shouldn’t be relying on a shared host for anything at scale, be that traffic, data size, etc
There are no bandwidth or storage limits on my DB plan
This sounds like ultra cheap slow shared hosting. They can promise you petabytes of storage because they know even if you tried to you could never get petabytes of storage on there.
Those limitations will depend almost entirely on what kind of queries you intend to do with those categories.
So I guess it’s just a matter of memory and CPU limitations, which I don’t know off hand.
I can point out a few things though:
- You won’t be able to list all categories on a page
- You should avoid helper functions WP provides such as
wp_list_categoriesand deal with the lower level APIs, aswp_list_categorieswill take too long - Never search for terms via their term meta
- Never do NOT IN style queries
The Fundamental Problem
2.75m unique categories is unmanageable, wether you can store it or not. I very much doubt you’re going to manually enter all that data yourself, or assign those categories. Listing them out, even with pagination, is unfeasible purely from a UI/UX point of view. Most of your challenges will have nothing to do with technical limitations, but there will be technical limitations.
To find those technical limits, your only true way of knowing is to try it. Either way this is one of those situations where something has probably gone terribly wrong, or you’re concealing something about the situation, perhaps to make it more generic in hopes of a better answer? Or an overabundance of caution related to your work.
Additionally, as I said earlier, these technical limits will have more to do with what kinds of queries and questions you ask the database, than the simple storage of them.
Otherwise nobody can say yes or no to this answer, it cannot be answered concretely with the information available