Make WordPress Core

Opened 7 years ago

Closed 4 years ago

Last modified 5 months ago

#14258 closed enhancement (wontfix)

wp_options and MySQL's "log-queries-not-using-indexes" config

Reported by: pkirk Owned by:
Milestone: Priority: normal
Severity: normal Version:
Component: Database Keywords:
Focuses: performance Cc:


Usually both on production and dev servers MySQL's config parameter "log-queries-not-using-indexes" is activated.

This produce a lot of log entries like that:

# Time: 100710 11:53:28
# User@Host: xxx[xxx] @ localhost []
# Query_time: 0.010032  Lock_time: 0.000085 Rows_sent: 275  Rows_examined: 337
SET timestamp=1278755608;
SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes';

Adding a simple index on the "autoload" field in wp_options table resolve the "problem".

Change History (7)

#1 @nacin
6 years ago

  • Keywords dev-feedback added
  • Milestone changed from Awaiting Review to Future Release

#2 @goatherd
5 years ago

What got me irritated too is the fact that autoload seems to be a boolean type (yes/no) yet is defined as varchar(20) of default database charset wasting up to 7 Bytes per row (for utf8_*).

It could be just as well defined as enum('yes', 'no') for single-byte storage and smaller index.

Also in some cases (slow machine, many options) the query reached well above 0.1 seconds without index.

#3 @pento
4 years ago

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

Adding an index won't improve performance for this. If a query going through an index reads more than 30% of the rows, MySQL will revert to a table scan.

There would be no benefit to switching to enum. Even assuming a ludicrously massive wp_options table of 5000 rows, the space saving would still be less than a single InnoDB page.

#4 @nacin
4 years ago

  • Keywords dev-feedback removed
  • Milestone Future Release deleted

Works for me.

#5 @nigro.simone
5 months ago

On MariaDB 10.1.9

SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes'
(262 total, Query took 0.0156 seconds.)

CREATE INDEX autoload ON wp_options(autoload, option_name);
SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes'
(262 total, Query took 0.0000 seconds.)

#6 @nigro.simone
5 months ago

  • Focuses performance added
Note: See TracTickets for help on using tickets.