WordPress.org

Make WordPress Core

Opened 4 years ago

Closed 21 months ago

Last modified 21 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: Cc:

Description

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 (4)

comment:1 nacin3 years ago

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

comment:2 goatherd2 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.

comment:3 pento21 months 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.

comment:4 nacin21 months ago

  • Keywords dev-feedback removed
  • Milestone Future Release deleted

Works for me.

Note: See TracTickets for help on using tickets.