Slow Query optimization in post.php (inc. solution)

Projects Core Development
1

Hi, this query recognized by Query Monitor as the “slow query” belongs to core WP/CP and ideed it’s very unoptimal. It’s runing on wp-admin/post.php to fulfill custom fields keys.

	SELECT DISTINCT meta_key
	FROM et_postmeta
	WHERE meta_key NOT BETWEEN '_'
	AND '_z'
	HAVING meta_key NOT LIKE '\\_%'
	ORDER BY meta_key
	LIMIT 30

query timing on localhost:
0.4180 s, (~500 000 rows in et_postmeta)
after cleaning the table:
0.0910 s. (~77 000 rows, still shows up as a slow query)

the code is located in /wp-admin/includes/template.php

zgrab-1754030578
zgrab-1754030578791×369 50.5 KB

Although it can be replaced by filter, but then the keys would be enforced and not dynamical so it’s not a good idea.

I have written the optimized version of this query, which is based on last posts with the same post_type, which is even more adequate in my opinion. I send it here as the purpose for the core ClassicPress development:

		// @jujist replace slow query to one running extra-fast ~0.00s.
		// param int $limit now is the number of last posts to check instead of limiting the keys
		
		$keys = $wpdb->get_col($wpdb->prepare("
			SELECT meta_key
			FROM $wpdb->posts as p
			LEFT JOIN $wpdb->postmeta as pm ON (p.ID=pm.post_id)
			WHERE post_type='$post->post_type' 
			AND SUBSTR(meta_key,1,1)!='_'
			ORDER BY ID DESC LIMIT 0, %d
		", $limit));
		$keys = array_unique($keys);

query timing on localhost: 0.0006 s in both cases (before/after cleanup)

QUERY TIME REDUCTION
original table 99,86% REDUCTION
after cleaning the table 99,34% REDUCTION

This query is really a bottleneck for post.php load time.
the begining time was 0.4535 s. for all SQL queries and 0.52 s. for page load
using the optimized query it’s 0.0380 s. for all SQL queries and 0.10 s. for page load

So with this single change of one query I have obtained 81% reduction of loading post.php

Let’s disclose the difference. The $limit parameter originally limits the number of keys based on alphabetical list, but in my query it will limit the number of last posts to check by the same post_type. In my opinion is okay to do it globally as it shows the full list of custom fields keys belongs to the recent X posts.

In real case before the change we could see a lot of junk keys stored many years ago,
and if there will be more than 30 (default limit value) different keys stored in the postmeta table, then we will not see the ones we really need

zgrab-1754032705
zgrab-1754032705593×570 27.1 KB

after the change we see the actual keys filled for X recent posts by the same post_type,
simple and bright :slight_smile:

zgrab-1754032124
zgrab-1754032124679×253 13.2 KB

1 Like
2

Thanks very much for this!

It’s well known that querying posts by starting in the postmeta table is very slow and should very much be avoided if possible as it can cause the site to freeze. So I’m wondering whether you have looked to see how many other queries there might be that could be improved in a similar fashion. It would be great to see if we could get them all upgraded.

3

Should I make pull request for this change?

As for other queries, I did not see any such significant one,
and my CP environment is already very clean:
the number of queries for particular admin pages:

  • plugins.php - 11
  • dashboard.php - 13
  • edit.php (article list) - 35
  • post.php - 47
    (there are custom taxonomies, custom columns taken from post_meta, and custom metaboxes),
    of course the load speed for each page is very impressive.

I would say it’s a beautiful proof for the ClassicPress usage and it’s advantages in a professional deployment and for large websites.

1 Like
4

Yes, please open a PR for this. I notice that the original query uses NOT LIKE whereas you have used !=. I’m wondering whether that means there’s a subtle change in what’s being searched for and whether that matters.

5

I used SUBSTR and exact match as a simpler form rather than searching for a pattern (using LIKE) but in this particular case there is not much difference I think. The slowest thing was searching through whole table and using DISTINCT. There is plenty of junk data here in wp_postmeta table starting from oembeds which is stored as a uique key for each occurence (core functionality), but the most junk are generated by plugins.

6

Yes, starting in the posts table makes a huge difference. Is there a way of testing what difference != makes compare to NOT LIKE? I’m just concerned that if we don’t keep to the latter, someone will complain in a year or so’s time that it’s not working as expected and we’ll have forgotten why

7

As I told you, it does not matter much in this particular case, it’s just a simpler form, probably the one who wrote the original SQL query did not know about the SUBSTR() function of SQL.
The basis of creating SQL query is:

  • use “=” if you can, if you can not, then use LIKE, be aware that it’s slower.

in the other words the difference is like:

  • show me everything what not looks like pattern ‘_’+anything
  • show me everything of which the first letter is not ‘_’
8

OK, are you happy to open a PR for this, or would you prefer that I do so instead?

9

Should the line ORDER BY ID DESC LIMIT 0, %d be ORDER BY ID DESC LIMIT %d?

10

thank you, good point about the LIMIT attribute, I have modified it and created a pull request.

1 Like
11

I have now opened a PR for this at Improve SQL query for retrieving custom fields (postmeta) on post edit screen by KTS915 · Pull Request #2073 · ClassicPress/ClassicPress · GitHub

12

@jujist

I was reviewing the Pull Request created with the aim of getting this improvement into the ClassicPress core but I’ve run into some issues - not sure if you can help.

The code we currently have proposed is:

$keys = $wpdb->get_col(
	$wpdb->prepare(
		"SELECT DISTINCT meta_key
		FROM $wpdb->posts as p
		LEFT JOIN $wpdb->postmeta as m ON p.ID = m.post_id
		WHERE post_type = '$post->post_type'
		AND SUBSTR(meta_key,1,1) != '_'
		ORDER BY ID DESC
		LIMIT %d",
		$limit
	)
);

The issues I am finding are:
1/ “selecting” meta_key from the posts table is invalid as that column doesn’t exist.
2/ “ordering” by ID is also invalid because it is not consistent with the select of the meta_key column

I believe the correct code syntactically would be more like this:

$keys = $wpdb->get_col(
	$wpdb->prepare(
		"SELECT DISTINCT meta_key
		FROM $wpdb->postmeta as meta
		LEFT JOIN $wpdb->posts as posts ON meta.post_id = posts.ID
		WHERE post_type = '$post->post_type'
		AND SUBSTR( meta_key, 1, 1) != '_'
		ORDER BY meta_key DESC
		LIMIT %d",
		$limit
	)
);

Do you have any comment of feedback on this?