WordPress.org

Make WordPress Core

Opened 7 years ago

Closed 6 years ago

#8561 closed defect (bug) (fixed)

query in meta_form() function in /wp-admin/includes/template.php causes post/page editing to load slowly

Reported by: fastpipe Owned by:
Milestone: 2.8 Priority: high
Severity: critical Version: 2.7
Component: Optimization Keywords: has-patch tested
Focuses: Cc:

Description

The function meta_form() on line 2329 of /wp-admin/includes/template.php contains this query:

	$keys = $wpdb->get_col( "
		SELECT meta_key
		FROM $wpdb->postmeta
		WHERE meta_key NOT LIKE '\_%'
		GROUP BY meta_key
		ORDER BY meta_id DESC
		LIMIT $limit" );

On my server, that particular query shows up in the mysql "slow log" whenever I go in to edit a post. Presumably, it's being used to populate the menu under the "Custom Fields -> Add a new custom field:" section of the new/edit post/page form.

Our wp_postmeta table currently has 428,438 rows so that particular query is particularly slow. Unless I'm missing something, a better replacement query would be:

	$keys = $wpdb->get_col( "
		SELECT DISTINCT meta_key
		FROM $wpdb->postmeta
		WHERE meta_key NOT LIKE '\_%'
		ORDER BY meta_key ASC
		LIMIT $limit" );

Running it on my server is more than twice as fast as the original. That removes the more resource expensive GROUP BY clause and sorts the result by the meta_key value, since using the meta_id for sorting is meaningless to populate the menu.

Attachments (2)

8561.diff (544 bytes) - added by vladimir_kolesnikov 7 years ago.
8561.2.diff (412 bytes) - added by Denis-de-Bernardy 6 years ago.

Download all attachments as: .zip

Change History (12)

comment:1 @vladimir_kolesnikov7 years ago

Actually there's no need in ORDER BY at all as natcasesort() is used to sort the retrieved array.

I would suggest this query:

SELECT DISTINCT meta_key
FROM wp_postmeta
WHERE meta_key NOT LIKE '\_%'
LIMIT {$limit}

@vladimir_kolesnikov7 years ago

comment:2 @vladimir_kolesnikov7 years ago

  • Component changed from Administration to Optimization
  • Keywords has-patch added

comment:3 follow-up: @mrmist7 years ago

It'll probably make some difference, but the biggest issue with the speed will probably be the WHERE NOT LIKE.

comment:4 in reply to: ↑ 3 ; follow-up: @vladimir_kolesnikov7 years ago

Replying to mrmist:
MySQL is able to use index for DISTINCT, WHERE ... NOT LIKE will be applied to the result of SELECT DISTINCT meta_key. Thus the speed of WHERE will depend on the cardinality of distinct meta keys.

The only alternative I see is to add an indexed column (say, 'special_key') which will be 1 for keys starting with '_' and 0 otherwise. Then MySQL will be able to use index both for WHERE and DISTINCT.

comment:5 @mrmist7 years ago

True. The 0 or 1 index would be low cardinatlity though so not that great. Either way your query is an improvement on the original I think.

comment:6 @jacobsantos7 years ago

  • Keywords changed from meta_key,postmeta,post,page has-patch to meta_key postmeta post page has-patch

comment:7 in reply to: ↑ 4 ; follow-up: @Denis-de-Bernardy6 years ago

patch is invalid. the group by is not the issue, and the distinct statement can only end up adding an extra sort operator to the full query, while doing the same as a group by.

the real issue is the order by meta_id desc. It should be order by meta_key instead, so as to use the index during the group by:

mysql> explain extended select meta_key from wp_postmeta where meta_key not like '\_%' group by meta_key order by meta_key limit 30;
+----+-------------+-------------+-------+---------------+----------+---------+------+------+---------------------------------------+
| id | select_type | table       | type  | possible_keys | key      | key_len | ref  | rows | Extra                                 |
+----+-------------+-------------+-------+---------------+----------+---------+------+------+---------------------------------------+
|  1 | SIMPLE      | wp_postmeta | range | NULL          | meta_key | 258     | NULL |   29 | Using where; Using index for group-by | 
+----+-------------+-------------+-------+---------------+----------+---------+------+------+---------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain extended select meta_key from wp_postmeta where meta_key not like '\_%' group by meta_key order by meta_id limit 30;
+----+-------------+-------------+-------+---------------+---------+---------+------+------+------------------------------+
| id | select_type | table       | type  | possible_keys | key     | key_len | ref  | rows | Extra                        |
+----+-------------+-------------+-------+---------------+---------+---------+------+------+------------------------------+
|  1 | SIMPLE      | wp_postmeta | index | NULL          | PRIMARY | 8       | NULL | 3472 | Using where; Using temporary | 
+----+-------------+-------------+-------+---------------+---------+---------+------+------+------------------------------+
1 row in set, 1 warning (0.00 sec)

@Denis-de-Bernardy6 years ago

comment:8 @Denis-de-Bernardy6 years ago

  • Keywords tested added; meta_key postmeta post page removed

comment:9 in reply to: ↑ 7 @vladimir_kolesnikov6 years ago

  • Cc vladimir@… added

Replying to Denis-de-Bernardy:

patch is invalid. the group by is not the issue, and the distinct statement can only end up adding an extra sort operator to the full query, while doing the same as a group by.

FYI: DISTINCT is the same as GROUP BY field ORDER BY NULL. Since the index is used for GROUP BY, no sort will take place (note the absense of Using filesort in EXPLAIN).

Although I agree that your query is a bit better as it allows to avoid creating a temporary table.

comment:10 @ryan6 years ago

  • Resolution set to fixed
  • Status changed from new to closed

(In [10998]) Improve meta_form() query. Props Denis-de-Bernardy. fixes #8561

Note: See TracTickets for help on using tickets.