WordPress.org

Make WordPress Core

Opened 4 years ago

Closed 3 years ago

#34869 closed defect (bug) (fixed)

dbDelta Index Definition Spaces Duplicate Indexes

Reported by: charlestonsw Owned by: ocean90
Milestone: 4.6 Priority: normal
Severity: normal Version: 2.8.1
Component: Database Keywords:
Focuses: performance Cc:

Description

Reference ticket #10404.

This is to decompose the original ticket into components. May be fixed in 4.4. Needs testing.


If the index definition contains spaces dbDelta will create duplicate indexes each time it is run.

No duplicates:

CREATE TABLE $wpdb->term_relationships (
 object_id bigint(20) unsigned NOT NULL default 0,
 term_taxonomy_id bigint(20) unsigned NOT NULL default 0,
 term_order int(11) NOT NULL default 0,
 PRIMARY KEY  (object_id,term_taxonomy_id),
 UNIQUE KEY reverse_pkey (term_taxonomy_id,object_id),
 KEY term_taxonomy_id (term_taxonomy_id)
) $charset_collate;");

Duplicates:

CREATE TABLE $wpdb->term_relationships (
 object_id bigint(20) unsigned NOT NULL default 0,
 term_taxonomy_id bigint(20) unsigned NOT NULL default 0,
 term_order int(11) NOT NULL default 0,
 PRIMARY KEY  (object_id,term_taxonomy_id),
 UNIQUE KEY reverse_pkey (term_taxonomy_id, object_id),
 KEY term_taxonomy_id (term_taxonomy_id)
) $charset_collate;");

Line of interest, note the space after the comma:

 UNIQUE KEY reverse_pkey (term_taxonomy_id, object_id),

Reported by: Denis-de-Bernardy

Change History (3)

#1 @charlestonsw
4 years ago

  • Component changed from General to Database
  • Focuses performance added

#2 @ocean90
3 years ago

  • Milestone changed from Awaiting Review to 4.6

#3 @ocean90
3 years ago

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

In 37583:

Database: Normalize index definitions in dbDelta().

dbDelta() compares the index definitions against the result of SHOW INDEX FROM $table_name. This requires a specific format so indices are not unnecessarily re-created. This format wasn't ensured, until now.

  • Parse the raw index definition to extract the type, name and columns so a normalized definition can be built (#20263, #34873).
  • Standardize on uppercase types (#34871) and on 'KEY'. 'INDEX' is only a synonym for 'KEY'.
  • Escape index names with backticks (#20263).
  • Normalize columns: Ignore ASC and DESC definitions (#34959), remove whitespaces (#34869) and escape column names with backticks (#20263).
  • Add backticks to all index change queries (#20263).

Props ocean90, pento, kurtpayne.
Fixes #20263, #34869, #34871, #34873, #34959.

Note: See TracTickets for help on using tickets.