Page MenuHomePhabricator

Make covering indexes unique
Closed, ResolvedPublic

Description

When these indexes are not unique, we will have to query 3 times in order to avoid duplication in these tables (check, insert, get id) when a record does not exist.

This is due a limitation in MW DBAL as there's no way yet to get the last insert id. This can be addded. However, making these indexes unique is both desirable and consistent with the purpose of preventing duplication in these tables.

Event Timeline

Restricted Application added a subscriber: Aklapper. · View Herald TranscriptApr 29 2019, 9:51 PM

Change 507211 had a related patch set uploaded (by Alaa Sarhan; owner: Alaa Sarhan):
[mediawiki/extensions/Wikibase@master] Make covering indexes unique in normalized wb_terms schema

https://gerrit.wikimedia.org/r/507211

alaa_wmde updated the task description. (Show Details)

After discussion we come to conclusion that we need to check, insert, get id anyway and check (first part) needs to happen on replica but the second ones can happen on master. The reason is that MySQL increases auto_increment last insert id with INSERT IGNORE even when it doesn't insert anything (this is avoid deadlocks and keeping the table locked for inserting a new row while inserting one is in progress)

Change 507211 merged by jenkins-bot:
[mediawiki/extensions/Wikibase@master] Make covering indexes unique in normalized wb_terms schema

https://gerrit.wikimedia.org/r/507211

alaa_wmde closed this task as Resolved.May 22 2019, 10:26 AM