Make WordPress Core

Opened 19 years ago

Closed 17 years ago

Last modified 9 years ago

#787 closed defect (bug) (fixed)

Bad PRIMARY key for the table wp_post2cat

Reported by: michel-v's profile michel v Owned by: rob1n's profile rob1n
Milestone: 2.3 Priority: normal
Severity: normal Version: 2.1
Component: Optimization Keywords: has-patch on-hold
Focuses: Cc:

Description

Using a rel_id field as a PRIMARY key to ensure relationships are unique is rather useless. We never use rel_id in the code.
A better approach would be to get rid of rel_id, and set the PRIMARY key on the tuple post_id and category_id.

Attachments (2)

ticket787patch.diff (1.4 KB) - added by ketsugi 18 years ago.
A quick patch: removes the rel_id columns from link2cat and post2cat, replaces the primary keys with a tuple, and bumps the database version by 1.
787.diff (1.5 KB) - added by rob1n 17 years ago.

Download all attachments as: .zip

Change History (20)

#1 @michel v
19 years ago

  • Patch set to No

#2 @matt
19 years ago

  • Status changed from new to assigned

#3 @markjaquith
18 years ago

still worth fixing?

@ketsugi
18 years ago

A quick patch: removes the rel_id columns from link2cat and post2cat, replaces the primary keys with a tuple, and bumps the database version by 1.

#4 @ketsugi
18 years ago

  • Component changed from General to Optimization
  • Owner changed from anonymous to ketsugi
  • Status changed from assigned to new
  • Version set to 2.0.4

#5 @foolswisdom
17 years ago

  • Milestone set to 2.1

#6 @matt
17 years ago

  • Milestone changed from 2.1 to 2.2

#7 @rob1n
17 years ago

  • Keywords dev-feedback 2nd-opinion added
  • Priority changed from low to normal
  • Severity changed from minor to normal
  • Summary changed from bad PRIMARY key for the table wp_post2cat to Bad PRIMARY key for the table wp_post2cat
  • Version changed from 2.0.4 to 2.1

Wouldn't setting both post_id and category_id as PRIMARY's force them to be unique? Neither will be unique, eventually...

#8 @DD32
17 years ago

Wouldn't setting both post_id and category_id as PRIMARY's force them to be unique?
Not if they're added as a tuple, ie a pair. The primary key would be a combination of post_id AND category_id.
I'm not sure myself if it'd be more optimized that way, or if it was a single index on either.

#9 @rob1n
17 years ago

  • Owner changed from ketsugi to rob1n
  • Status changed from new to assigned

I'd say a simple INDEX on both category_ID and post_ID would be nice. Any devs have feedback?

#10 @rob1n
17 years ago

Oh, and taking out rel_id too.

@rob1n
17 years ago

#11 @rob1n
17 years ago

  • Keywords has-patch added; 2nd-opinion removed

#12 @matt
17 years ago

I like the new primary key. I think it's worth also having an index on category_id re #3900.

#14 @rob1n
17 years ago

  • Keywords on-hold added; dev-feedback removed

I'll take another look when #3900 is committed.

#15 @rob1n
17 years ago

  • Milestone changed from 2.2 to 2.3

#16 @rob1n
17 years ago

  • Status changed from assigned to new

#17 @ryan
17 years ago

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

post2cat is gone in 2.3.

This ticket was mentioned in Slack in #cli by voldemortensen. View the logs.


9 years ago

Note: See TracTickets for help on using tickets.