Speed up Toolset Archive Views

WPSOLR will automatically speed up Toolset Archive Views

You do not have to choose between flexibility and scalability anymore. Keep using Toolset Views plugin as your favourite Query Builder. But boost your site speed and relevancy with WPSOLR working in the background.

Bring the speed of WPSOLR to Toolset Archive Views

With Toolset Views alone, you can build sophisticated queries and filters. Without any knowledge of SQL. But the more complex your queries, the less quick your views become. In fact, SQL simply slows down with more where clause, table join, table scan, and aggregation.

To see how SQL statements are built with the Toolset Views Query Builder, let’s install the plugin Query monitor. This plugin displays all SQL queries executed while the admin or front-end pages are loaded.

Why SQL full-text search queries are slow?

A full-text search is a search with keywords. For instance, the SQL query below is a search for keyword ‘red’. From the SQL, you can notice several facts.

First, the keywords are searched in 3 fields only (title, excerpt, content). Custom fields and products attributes are not searched at all.

Secondly, the keywords are partially searched. The special character ‘%’ is added at the beginning and at the end of the keyword, to tell mySQL to look for posts beginning with, and ending by ‘red’. This is a tentative to also retrieve related keywords, like plural forms (“red”, “reds”, …). But this is hugely costly, because the full-text search is not using any index. The search is opening every title/excerpt/content and tries to match it to the keywords.

And obviously, a search that opens every post for every search is not scalable. The more posts you add, and the more visitors you get, the more your server will need CPU/RAM/Disk. At some point, you will not be able to afford enough server resources and you search will crash.

SELECT SQL_CALC_FOUND_ROWS posts.ID FROM posts WHERE 1=1 AND (((posts.post_title LIKE '%red%') OR (posts.post_excerpt LIKE '%red%') OR (posts.post_content LIKE '%red%'))) AND posts.post_type = 'post' AND ((posts.post_status = 'publish' OR posts.post_status = 'private'))

Why complex SQL queries are slow?

The nicest feature of Toolset Views can be your main downfall. It is so easy to add complex filters to your views. Dates, authors, categories, tags, custom fields, and so on. There are also many comparators to choose from. BETWEEN, IN, NOT IN, ANY, NONE, >, <, and so on.

Just remember that each new filter will create more stress on your server. This is probably fine if you manage few posts/products, or get few visitors.
But as soon your site becomes somehow popular, all those filters will contribute to slowing it down.
Eventually to the point where it can barely serve any search under 3 seconds. And 3 seconds is considered today the maximum time a visitor will be willing to wait, before leaving to your competitors. But you also probably know that speed is a key SEO factor that Google will consider, with many others, to position your site before your competition too.

a) This simple Toolset View filter on date and taxonomy:

b) will generate this complex and costly SQL:

SELECT SQL_CALC_FOUND_ROWS posts.ID FROM posts LEFT JOIN term_relationships ON (posts.ID = term_relationships.object_id) WHERE 1=1 AND ( ( YEAR( posts.post_date ) = 2018 AND MONTH( posts.post_date ) = 12 AND HOUR( posts.post_date ) = 10 ) OR YEAR( posts.post_modified ) BETWEEN 2018 AND 2013 ) AND ( term_relationships.term_taxonomy_id IN (1,2,3,4,5,6,7) AND posts.ID NOT IN ( SELECT object_id FROM term_relationships WHERE term_taxonomy_id IN (9,19) ) ) AND (((posts.post_title LIKE '%red%') OR (posts.post_excerpt LIKE '%red%') OR (posts.post_content LIKE '%red%'))) AND posts.post_type = 'post' AND ((posts.post_status = 'publish' OR posts.post_status = 'private')) GROUP BY posts.ID

Here, we get everything feared by SQL admins:
– Table joins
– Functions in where condition
– Full-text search
– Results aggregation

In severe conditions, it can only lead to server resource exhaustion and search downtime.

Bring the simplicity of Toolset Views to WPSOLR

With WPSOLR alone, queries are incredibly fast and scalable. Including dynamic facets to help filtering results with custom fields or product attributes. And of course, with WPSOLR’s hooks, you can develop more complicated queries to suit your needs. But the hooks require quite a heck of a knowledge about Elasticsearch and Solr. Therefore, until now, there was no easy way to build custom queries without an expertise search engine knowledge. Unlike the Toolset Views Query Builder.

Below is an example of the complexity of Elasticsearch queries. This simplified one was generated automatically from the Toolset Views Query Builders b) example from the previous paragraph:

And remember that this is a pretty simple query.

But then, why is so different from the previous SQL query, in terms of performance?

Well, the answer is “everything”. Because Elasticsearch was built to handle such sophisticated queries, by using an inverted index.

Inverted indexes are used like tables of contents in books. Instead of a page number as in books, they store, for each word, which document contains the word. This enables to retrieve instantaneously every post containing the word “red”, without opening any post. And there are many ways, named analysers and filters, to customize the inverted index content to specific needs. Like for languages, stop words, synonyms, stemming, and so on.

If you wish to see by yourself a full tutorial showing the setup of WPSOLR (since version 20.1), to speed up Toolset Archive Views: