WordPress.org

Make WordPress Core

Opened 5 years ago

Closed 21 months ago

#9435 closed enhancement (fixed)

DB schema clean-ups

Reported by: Denis-de-Bernardy Owned by:
Milestone: 2.8 Priority: normal
Severity: minor Version: 2.8
Component: Optimization Keywords: has-patch
Focuses: Cc:

Description

While looking into #9422, it occurred to me that some DB fields weren't necessarily used today. Namely:

links.link_category int(20) seems to be overridden by the terms schema.

The same goes for posts.post_category int(4).

It might be that they've been left around for obsolete plugins, though. In case this isn't the case, the attached patch, for 2.8 trunk, will remove them.

Attachments (6)

9435.diff (967 bytes) - added by Denis-de-Bernardy 5 years ago.
9435-int-11.diff (7.1 KB) - added by Denis-de-Bernardy 5 years ago.
use int(11) rather than bigint(20) unsigned, for smaller indexes (and thus faster queries)
9435-text-fields.diff (3.2 KB) - added by Denis-de-Bernardy 5 years ago.
more relevant text fields: text is 65kb, mediumtext is 16MB, longtext is 4GB - an rss field stored in wp_options is only rarely larger than 65kb, and no post would ever be larger than 16MB
9435-text-fields-alt.diff (2.8 KB) - added by Denis-de-Bernardy 5 years ago.
the same patch, keeping comment_author and post_title as text fields in case this was needed for any reason
9435-smaller-int-fields.diff (2.5 KB) - added by Denis-de-Bernardy 5 years ago.
smaller int fields. surely no post will have over 65k comments?
9435-user-status.diff (1.5 KB) - added by Denis-de-Bernardy 5 years ago.
drop user_status field -- see http://comox.textdrive.com/pipermail/wp-hackers/2009-January/023550.html

Download all attachments as: .zip

Change History (16)

Denis-de-Bernardy5 years ago

comment:1 Denis-de-Bernardy5 years ago

  • Keywords needs-testing added

Marking as needs-testing, in case their absence has any impact I've missed by scanning the source code.

Denis-de-Bernardy5 years ago

use int(11) rather than bigint(20) unsigned, for smaller indexes (and thus faster queries)

Denis-de-Bernardy5 years ago

more relevant text fields: text is 65kb, mediumtext is 16MB, longtext is 4GB - an rss field stored in wp_options is only rarely larger than 65kb, and no post would ever be larger than 16MB

Denis-de-Bernardy5 years ago

the same patch, keeping comment_author and post_title as text fields in case this was needed for any reason

Denis-de-Bernardy5 years ago

smaller int fields. surely no post will have over 65k comments?

comment:2 ryan5 years ago

(In [10895]) Drop post_category and link_category from the schema. Props Denis-de-Bernardy. see #9435

comment:3 MichaelH5 years ago

Is this correct?

By dropping post_category and link_category, new installs will not see those columns, but upgraded databases will still have the two columns.

comment:4 ryan5 years ago

Correct. dbDelta() doesn't handle deleting old columns.

comment:5 ryan5 years ago

user_status, BTW, is needed by bbPress.

comment:6 Denis-de-Bernardy5 years ago

  • Keywords dev-feedback added; needs-testing removed

I take it the other ones are not to be used? close as fixed, or move to 2.9 for the text/varchar/int cleanups?

comment:7 ryan5 years ago

  • Milestone changed from 2.8 to 2.9

2.9 sounds good for the type cleanups.

comment:8 Denis-de-Bernardy5 years ago

  • Keywords dev-feedback removed

comment:9 ryan4 years ago

  • Milestone changed from 2.9 to Future Release

comment:10 nacin21 months ago

  • Milestone changed from Future Release to 2.8
  • Resolution set to fixed
  • Status changed from new to closed
Note: See TracTickets for help on using tickets.