A site that is slow because of the database almost always has one or two queries doing all the work, and the most frequent fix is a missing index. The route is this: find which query it is, ask MariaDB to explain how it runs it (EXPLAIN), and only then fix it. Poking blindly, or changing plan, rarely helps.
Finding the guilty query
| 1 |
In WordPress, install the Query Monitor plugin: it shows each page’s queries, slowest first, and the plugin or theme that asked for them.
|
|
| 2 |
In another application, watch the running queries in phpMyAdmin, under Status, then Processes, while the page is slow. A query that keeps appearing is the suspect.
|
|
| 3 |
On a VPS of your own, you can switch on the slow query log in the engine (slow_query_log and long_query_time). On shared hosting those settings belong to the server, not to you.
|
|
Asking MariaDB to explain
On the phpMyAdmin SQL tab, type EXPLAIN before the suspect query and run it. A type column with the value ALL means the engine read the whole table, row by row. A value such as ref or const means it used an index. The rows column shows how many rows it expects to read.
| Fix |
When it works |
Create an index on the WHERE or JOIN column |
EXPLAIN shows ALL on a large table. The most common one. |
Ask only for the columns you need, and add LIMIT |
The application does SELECT * and shows ten rows out of a million. |
| Shrink the database |
Tables full of old records, revisions, sessions and temporary data. See where to see its size and what can go. |
| Clear the options loaded on every page |
In WordPress, a wp_options table with a lot of data marked autoload. |
| Page cache |
The same query runs on every visit. See installing and tuning a page cache. |
More indexes is not always better. Each index speeds up reading and slows down writing, and takes space. Create one at a time, on the column EXPLAIN pointed to, and measure. And make a copy first: see exporting with phpMyAdmin.
|
An example, start to finish
A shop is slow to list orders by status. You run EXPLAIN SELECT * FROM orders WHERE status = 'paid'; and the type column says ALL, with many estimated rows. You create CREATE INDEX idx_status ON orders (status); and repeat the EXPLAIN: now type is ref and the estimated rows drop sharply. The page that took a while to open now answers at once. It was a single index, chosen because EXPLAIN asked for it. That is the idea: measure, change one thing, measure again.
|
Found the query but not sure what to do next? Send us the EXPLAIN output and the application and we will look at it with you.
Open a support ticket
|
RECOMMENDED PRODUCT Web hosting with cPanel Domain and SSL included, daily backups and the panel you already know. from 5.940,00 Kz/mo (3-year plan, with coupon) See plans |
Português
English
