Skip to:

Opened 12 years ago

Closed 12 years ago

Last modified 12 years ago

#4104 closed defect (bug) (fixed)

wp_bp_activity stores item_id and secondary_item_id as VARCHAR; breaks indexes

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


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 12 years ago.

Download all attachments as: .zip

Change History (6)

#1 @boonebgorges
12 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
12 years ago

Looks good here.

#3 @DJPaul
12 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
12 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
12 years ago

Confirmed. Looks good to me. Nice work.

Note: See TracTickets for help on using tickets.