Skip to:
Content

BuddyPress.org

Opened 10 years ago

Closed 10 years ago

Last modified 10 years ago

#4104 closed defect (bug) (fixed)

wp_bp_activity stores item_id and secondary_item_id as VARCHAR; breaks indexes

Reported by: boonebgorges Owned by:
Milestone: 1.6 Priority: normal
Severity: major Version:
Component: Activity Keywords: has-patch needs-testing
Cc:

Description

The item_id and secondary_item_id columns in wp_bp_activity are varchar(75). This is bad in a couple of ways:

  • When querying on a specific item/secondary item, our query class casts the items to int (in clauses like WHERE a.item_id IN ( 5,8,20 )). The type mismatch between our query and the column means that MySQL can't use its indexes and has to do a full tablescan.
  • varchar is less efficient when an integer type would work
  • We're casting to int before writing to the activity table anyway

Should just be able to change the table definition and let dbDelta() do its work, but this will need some testing.

Attachments (1)

4104.01.diff (967 bytes) - added by boonebgorges 10 years ago.

Download all attachments as: .zip

Change History (6)

#1 @boonebgorges
10 years ago

  • Keywords has-patch needs-testing added

4104.01.diff changes the schema and bumps the db number to prompt an upgrade. Looks good on my local, but I would like a second opinion before committing.

#2 @DJPaul
10 years ago

Looks good here.

#3 @DJPaul
10 years ago

I'm going to commit this on your behalf so we can get it tested from people running trunk. It seems solid from my testing.

#4 @djpaul
10 years ago

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

(In [5955]) Correct item_id column types in activity table schema. All props to boonebgorges, fixes #4104

#5 @johnjamesjacoby
10 years ago

Confirmed. Looks good to me. Nice work.

Note: See TracTickets for help on using tickets.