Hi,
i’ve some issue with mariadb 10.6.16 and this sql query:
SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts LEFT JOIN wp_postmeta AS sq1 ON ( wp_posts.ID = sq1.post_id AND sq1.meta_key = '_wp_attached_file' ) WHERE 1=1 AND (((wp_posts.post_title LIKE '%tcl%') OR (wp_posts.post_excerpt LIKE '%tcl%') OR (wp_posts.post_content LIKE '%tcl%') OR (sq1.meta_value LIKE '%tcl%'))) AND wp_posts.post_type = 'attachment' AND ((wp_posts.post_status = 'inherit' OR wp_posts.post_status = 'private')) GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 20
The query took 35/40 seconds to show records.
I’ve another vps with Centos 7 and MariaDB 5.x and the same query took 2 seconds in the same db.
How can i solve this issue ?
Thanks
just think through what that long query is attempting to do (in such a convoluted way) it almost certainly should have been split into smaller requests. As wp_posts and wp_postsmeta grows each of those LIKE searches are going to consume resource. Even worse if being repeatedly requested - let the db maintain a VIEW.
Are these InnoDB tables?
There have been many changes to MariaDB since v5.x (some - not all, are improvements).
As for solving it - that is really down to the person who coded whatever plugin you are using in WP. I doubt if it has anything to do with your OS or Webmin
May be not the wp plugin but the mysql query that it runs, from looking at other topics here and elsewhere wp can become uncontrollable very quickly, without prior knowledge of how wp works. I have never used wp, apart from in a contained enviroment which led me to the above conclusion
Where in the wp code base is that query ? Is in the wp core ? If so i would report this back to the wp devs to investigate, as pointed out earlier there are changes between different versions of mariadb or mysql, so this query may need to be rewritten to take into account of these changes
Are the databases identical between the two? (I mean, the same contents?)
I would expect this query to grow in time based on number and size of posts. You might also check to be sure your my.cnf has similar cache settings across the two systems. Also compare disk performance and how much available memory you have.
Adding indexes to some of the tables in this query could potentially help speed up queries, if they don’t already have them. You’re doing a full-text search on post_content which seems likely to be slow-ish if you have a lot of big posts.
As others note, this is a Mariadb question, rather than Virtualmin/Webmin, and we’re possibly not the best folks to answer technical Mariadb questions (I know more than average, but I’m definitely not an expert).
running on phpMyAdmin is not a fair comparison. (it is a completely different environment) even if you are looking at the same WP tables (does it understand current WP activity)
It is still pretty clear to me that query is badly constructed - even if the tables are well indexed and flat/normalised. (which I doubt). tables described as “meta” in particular often have complex indexes and reference other tables that can have other triggers and active routines (so on a busy website are constantly changing/re-indexing and using cpu - can your system cope with that? - how many websites are busy/how much other activity is running email etc) Is this simply yet another OOM waiting to happen.
As has been pointed out those two tables are almost certainly big (and I would guess poorly maintained - the last thing most coders think of)
You need to find where in the php code that query is being executed and address the issue directly at the coder responsible. Most modern php code uses PDO and no longer uses mysqli and moving the code base is not simple (hence my finger pointing at a WP plugin)
