- Early access to new free plugin which speeds up imports even more! - October 14, 2024
- ChatGPT really sucks at SQL - August 9, 2024
- We’ve launched our newsletter again - July 24, 2024
One of our customers who has been enjoying the speed benefits of Super Speedy Search, in particular with relation to how much it is speeding up his wp-admin speed, came to me yesterday with an issue.
On this other website, his link builder searches in Gutenberg were taking 120 seconds on the first attempt and then 25 seconds thereafter. It’s not uncommon for searches to become faster on the subsequent searches once all the data is in the cache but clearly both 120 and 25 seconds search time is totally unacceptable.
This customer has a large news site with 700,000+ news articles, but even if you have smaller sites, if you have only 1,000 items for example, you may have 100,000+ or more entries in wp_postmeta in which case your searches will be slow without our plugin. Slow searches are incredibly annoying as a WordPress administrator which is why we’re ending them completely.
Adding Super Speedy Search to our Scalability Pro plugin which speeds up everything else wp-admin related will make you and your admins lives far less miserable!
Table of Contents
Speeding up Link Builder Search in the WordPress Dashboard
Directly as a result of this request from this customer, I dove back into the code of my Super Speedy Search plugin and traced the code to find out why it was not being optimised automatically.
It turned out there were a few things happening. Firstly, WordPress was joining my wp_superspeedysearch table to wp_postmeta when I handle postmeta search in a faster way, so I fixed that.
Secondly, WordPress is performing a slow ajax media filename search. You may need this, but most likely you do not (not for link builder) and the filename search occurs against wp_postmeta with an open ended LIKE query, so it’s incredibly slow. I’ve added an option to the Super Speedy Search settings page to disable this filename search from within link builder. Filename search will continue to work from the media search.
Lastly, WordPress performs a tag search for your link builder searches. This is more relevant than filename searches but a whole extra roundtrip to the server prior to finishing does add perceptible time to the activity. So, I’ve added an option to Super Speedy Search to remove this tag search from link builder too.
The results are, as always, lightning fast. Instead of waiting 10 seconds or more for link builder to respond, you now get responses within a second.
Speeding up Post and Page search in the WordPress Dashboard
If you are finding edit.php to be incredibly slow when searching for posts or pages or custom post types (if the CPTs use edit.php) then this additional update I made to Super Speedy Search will make you very happy. This same customer, with 700,000 posts on a large news website, was finding their searches on edit.php (where you view lists of your latest posts or pages in wp-admin WordPress dashboard) taking over 120 seconds.
Again, this search was defaulting to joining to wp_postmeta but this time in order to search the filenames at the same time as searching the post content. On top of that, my default optimisations for your front end were not as useful here in the backend – the admins do not care as much about your front end ‘weights’ that you can configure to rank your front-end results and instead are just looking for speed and accuracy.
So, I updated the override here to rely on the default ‘relevance’ search that comes out the box with a MySQL fulltext index and I reduced the columns checked to just check against post_title, post_content and post_excerpt. From the front-end, normally Super Speedy Search searches these 3 columns plus the additional meta_values and taxonomy_values columns added to our denormalised wp_superspeedysearch table.
As a result, the speed of searches in edit.php has been reduced to about 0.3 seconds for this customer with 700,000+ news articles. Once this goes live on his main site, his admins are going to be incredibly happy!
There is one more potential issue – if the data is not entirely in RAM then all the data must be fetched in order to calculate the total matches.
In the screenshot above, you can see SQL_CALC_FOUND_ROWS – this part calculates the total matches which you can see is 190,000. Only 15 rows are actually returned on this page, so using the option inside Scalability Pro to remove this item and fake pagination would bring even more speed boosts. This is why Scalability Pro and Super Speedy Search are frequently purchased together. In fact, typically people buy the Super Speedy Pack which also includes our Super Speedy Filters plugin.
Super Speedy Search is truly multilingual and incredibly fast at speeding up your wp-admin searches
I’m happy to report that these speeds are all with the Persian/Arabic right-to-left language. If you are using English, your speeds should be similar if not slightly improved. On top of that, unlike third-party services like Algolia – who consider speeding up wp-admin to be out of scope for a search plugin – we want to speed up ALL of your search.
If any customers find any other searches behaving slowly, for any custom post types or anything else not already speeded up, let me know through our Discord server or in the comment section below and I’ll get to work on another optimisation.
Fast WordPress Admin Searches Demo
Below you can watch our before and after video based on this clients website. You’ll see fast searches with truly no limit. This client happens to have 720,000 news articles, but because of how indexes work there really is no limit to the size
