- 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
A customer of mine was struggling with an import. After activating the Slow Query Log inside Scalability Pro I discovered they had a slow query coming from a plugin called WPNotif. This plugin is sold by Code Canyon and lets you send WhatsApp messages to your customers – quite cool!
They have a slow query however which you won’t notice at first, but once your site gets larger you’ll notice a big slowdown which hurts your server.
WPNotif’s table structure
They create a table called wpnotif_wc_cart_sessions. This table comes with 2 indexes by default but no index for the slow query we discovered.
Adding a suitable index for making the WPNotif plugin faster
The slow query is doing an equals operation on the notif_status column and a less-than operation on the time column. When it comes to matching indexes up to queries, the order of the columns in the indexes matters. Basically – any columns where the query performs an equals operation should come first in the index with greater-than and less-than columns coming at the end of the index.
MySQL will benefit from an index when performing a less-than or greater-than operation but it can only really optimise ONE of these operations in the query. After that, the remaining data needs to be scanned.
So – that means our index should be on notif_status and then time.
Now the query takes 0.001s instead of 0.329s. Note this would only have gotten worse for this customer – the more customers and cart sessions they had, the slower their website and notifications service would have become.
