MariaDB Slow Query

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

SYSTEM INFORMATION
OS type and version Ubuntu Linux 22.04.4
Webmin version 2.111
Usermin version 2.010
Virtualmin version 7.10.0
Theme version 21.10
Package updates All installed packages are up to date
PHP Version: 8.3.7

That’s not really a webmin issue but a mariadb issue. All webmin is is a GUI interface.

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

I know but I didn’t know which category to write the post :slight_smile:

Ok thanks :wink:

I normally google the issue. This sounds to good to be true.

I’ve already done the suggest, but didn’t work for me.

I’ve tried the query on phpmyadmin and the time response it’s the same.

the same as “35/40 seconds” or the same as “2 seconds”?

It is still a WP (probably plugin) problem not 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

30 seconds…
but if you use phpmyadmin i don’t think it’s a issue about plugin.

I created the post in Webmin because I didn’t know in which category to write the post, the admins can move it to the correct category if they want.

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

1 Like

when you search a media

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)

has your provider restricted the disk IOPs?

Albeit it on my cheap hosting provider they heavily restricted the IOPs.

I am not an expert, but are the table types still the same i.e. INNODB/MyISAM

Is there table or row locking going on with another SQL.

i’ve a VPS so i don’t think that the provider restricted the disk IOPs.

The db it’s the same, i moved from another vps to this new one.