Add explicit PRIMARY KEY for select heavy tables with a multi-column UNIQUE KEY

[TimWolla]

InnoDB uses the PRIMARY KEY as the clustered index for the btree. Without an explicit PRIMARY KEY an internal auto increment will be used, allowing the best INSERT performance because data always is appended to the end.

Setting an explicit PRIMARY KEY might reduce INSERT performance due to insertions in the middle, but will improve SELECT performance, because the data is stored continuously. In addition this removes the need for the secondary index, making the table smaller.

see also: https://www.percona.com/blog/2018/07/26/tuning-innodb-primary-keys/

We should leverage this for read heavy tables:

• [x] wcf1_article_content:
  • [x] Use PRIMARY KEY (articleID, languageID).
  • [x] Remove UNIQUE KEY (articleID, languageID).
• [x] wcf1_box_content:
  • [x] Maybe use PRIMARY KEY (boxID, languageID).
  • [x] Remove UNIQUE KEY (boxID, languageID).
• [x] wcf1_like:
  • [x] Maybe use PRIMARY KEY (objectTypeID, objectID, userID).
  • [x] Remove UNIQUE KEY (objectTypeID, objectID, userID).
• [x] wcf1_like_object:
  • [x] Maybe use PRIMARY KEY (objectTypeID, objectID).
  • [x] Remove UNIQUE KEY (objectTypeID, objectID).
• [x] wcf1_media_content:
  • [x] Maybe use PRIMARY KEY (mediaID, languageID).
  • [x] Remove UNIQUE KEY (mediaID, languageID).
• [x] wcf1_page_box_order:
  • [x] Use PRIMARY KEY (pageID, boxID).
  • [x] Remove UNIQUE KEY pageToBox (pageID, boxID).
• [x] wcf1_page_content:
  • [x] Use PRIMARY KEY (pageID, languageID).
  • [x] Remove UNIQUE KEY (pageID, languageID).
• [x] wcf1_poll_option_vote:
  • [x] Maybe use PRIMARY KEY (pollID, optionID, userID).
    • Note: I wonder why pollID even is part of this table, because it can be derived from optionID.
    • [x] Maybe drop the pollID column, the additional join when voting / listing votees shouldn't be too bad.
  • [x] Remove UNIQUE KEY vote (pollID, optionID, userID).
• [x] wcf1_tag_to_object:
  • Currently: UNIQUE KEY (objectTypeID, languageID, objectID, tagID)
  • [x] Possibly move objectID in front of languageID.
  • All in all this table is pretty wonky. The languageID can be derived from tagID.
  • :point_right: Needs further checking, but a single PRIMARY KEY (objectTypeID, objectID, tagID) without any secondary indices should work.
• [ ] wcf1_user_collapsible_content:
  • [ ] Use PRIMARY KEY (userID, objectTypeID, objectID)
    • Moving userID to the front.
    • I mostly expect this to be queried for a fixed userID.
  • [ ] Remove UNIQUE KEY (objectTypeID, objectID, userID).
• [x] wcf1_user_group_option_value:
  • [x] Use PRIMARY KEY (groupID, optionID).
  • [x] Remove UNIQUE KEY groupID (groupID, optionID).
• [x] wcf1_user_special_trophy:
  • [x] Use PRIMARY KEY (userID, trophyID)
    • Inverted order, because I expect this to mostly search for trophies of a single user.
  • [x] Remove UNIQUE KEY (trophyID, userID)
• [x] wcf1_user_to_group:
  • [x] Use PRIMARY KEY (userID, groupID)
  • [x] Possibly add KEY (groupID)
    • “find all users in group”.

[TimWolla]

* `wcf1_like`:

The workload is not clear enough to justify a change here. In addition the INSERTs will be “all over the place” due to userID being part of the INDEX and users possibly reacting to oldish content.

[TimWolla]

* `wcf1_poll_option_vote`

This one is barely read from during normal operation. This does not justify a change.

[TimWolla]

* `wcf1_article_content`

That one already has a PRIMARY KEY that I must've missed.

[TimWolla]

* `wcf1_box_content`

Same here.

[TimWolla]

* `wcf1_page_content`

Same.

[TimWolla]

* `wcf1_user_collapsible_content`

This one is cached in user storage and there's the resetAll() method that includes a query that does not include the userID. I'm not that sure about the benefits of my suggestion here.

[TimWolla]

Use PRIMARY KEY (userID, trophyID)

* Inverted order, because I expect this to mostly search for trophies of a single user.

The table is queried with a DELETE on trophyID when a trophy is being disabled. I expect that to rarely happen.

[TimWolla]

Possibly add KEY (groupID)

* “find all users in group”.

This one was already created implicitly, because of the FOREIGN KEY.

[TimWolla]

* `wcf1_like_object`

Unfortunately that one also has a PRIMARY KEY with auto increment. And the contents of this table are mapped as a DatabaseObject, so we can't adjust this one either.

[dtdesign]

* `wcf1_like_object`

Unfortunately that one also has a PRIMARY KEY with auto increment. And the contents of this table are mapped as a DatabaseObject, so we can't adjust this one either.

Why is that a problem? The AUTO_INCREMENT is not required to be the PK.

[TimWolla]

Why is that a problem? The AUTO_INCREMENT is not required to be the PK.

Interesting. I was of the impression that this had to be the case. This changes everything :grin:

[TimWolla]

Moving this to 5.5 to revisit it then:

• The important tag table already was cleaned up.
• I am not super happy with the auto increment column not being the primary key. I feel like this can cause unexpected issues. If I wasn't aware that this was even possible, then I wouldn't be surprised if others are not either.