Only when you get to work on a really huge WordPress website with hundred thousands of posts, post tags and other elements, you will realize that many operations you think as common are going to get very, very slow even on very powerful servers. What to do than?
In some cases, it’s best to give on WordPress and make your own CMS that is made for the need you have. Getting some other CMS is most likely not good solution since it will have some other issue that will cause troubles, since all CMS have one fatal problem: they are not made for a single purpose, they are made to cover all sorts of uses, and this type of generalization will always be slower than custom use specific coding. But, I don’t plan just to give up on WordPress for such complex tasks, best is to make changes that will solve the problem.
Main problem and major slowdown on the AppHelp website was getting list of posts belonging to a post tag. One run of the query can take up to 1 minute (yes, one whole minute), and that’s not good. Using cache plugin is partial solution, and I prefer to have something that will solve the issue at the source, not to patch it after.
Solution to this problem is to change the SQL query WP generates to get posts archive based on tags. The solution I have in mind takes 2 queries instead of 1, and these 2 queries need only 0.2 seconds to run. Compare that to 30 seconds (on average) original query takes for such large database, and you will see that new method is 100 times faster! Minor downside is that mySQL for these 2 queries will use a bit more memory (I managed to measure some 15% increase), and in my book that is a fair trade off for the massive speed improvement we got.
Original WP code gets post tag (term) slug and makes a query that joins posts table with all 3 taxonomies tables. If you want to use multiple terms SQL where clause will try all of them in the same time, and in all cases will search by VARCHAR field. So, there are actually two solutions. First, and simpler one is to use term ID’s in where, instead of slug names. This is actually method WP uses to filter categories and category pages are much faster than post tags because of simple SQL. But, that method is still slow, as it takes up to 10 seconds to run, and we want to get it much, much faster.
My solution is to add one more step. First, get the term ID’s for all terms. Than, make a new SQL query that will join posts table directly to the secondary, nested SQL query that will use term ID’s. Such method is very, very fast.
So, how to actually do this? Since we need to change whole query not only parts of it, there is only one way to do it without actually hacking WP_Query class. We need to hook up after the query is created and than make our own query. To do that we need to add filter for ‘posts_request’. This will give as full query made by WP_Query object, and the current instance of WP_Query object. Since, only problem are taxonomy pages (mostly tags in my case) code will check out if we are on the right page.