As a WMF DBA I want the production SQL tables to match what the developers work on to avoid unexpected issues.
As a Wikibase developer I want the SQL in code to match what is used in production in order to avoid accidently breaking production.
Description
At some point in 2018 we thoughts the index "tmp1" that exists in WMF production for wikidata on the wb_terms table was not used.
While removing the index we had a rather large outage, probably to do with some usage of the index, and thus the removal was reverted.
In order to being our code SQL files in line with WMF production this index should be re added to our code.
T197854 discusses renaming the index, but this process would take weeks, and as our end game is to totally remove the table there is little point in spending many weeks of DBA time doing the rename.
If we do find that we actually want to still use the table in the future we can re-re evaluate this
Impact & Priority
The tmp1 index caused a massive outage, we want to avoid doing that in the future, although I don't see if forgetting about it in the near term.
This task is small and should be easy to complete so lets get it done and avoid it sitting here for a year, at which point we may have forgotten about the hardships that it caused.
Task
Add the SQL code for the tmp1 index on wb_terms to the Wikibase code base
Acceptance criteria
- On Wikibase install the wb_terms table should exist with the tmp1 index.
- On Wikibase update the tmp1 index should be added to wb_terms if it did not already exist.
- The tmp1 index should match the index that is used in WMF production.
- A comment next to the index should be added pointing to this ticket so it can easily be found if a developer looks at the code in 2 years time.
SQL Details
As of 20/08/2018
MariaDB [wikidatawiki]> describe wb_terms; +---------------------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------------+---------------------+------+-----+---------+----------------+ | term_row_id | bigint(20) unsigned | NO | PRI | NULL | auto_increment | | term_entity_id | int(10) unsigned | NO | MUL | NULL | | | term_full_entity_id | varbinary(32) | YES | MUL | NULL | | | term_entity_type | varbinary(32) | NO | | NULL | | | term_language | varbinary(32) | NO | MUL | NULL | | | term_type | varbinary(32) | NO | | NULL | | | term_text | varbinary(255) | NO | MUL | NULL | | | term_search_key | varbinary(255) | NO | MUL | NULL | | | term_weight | float unsigned | NO | | 0 | | +---------------------+---------------------+------+-----+---------+----------------+ 9 rows in set (0.00 sec) MariaDB [wikidatawiki]> SHOW INDEX FROM wb_terms; +----------+------------+---------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +----------+------------+---------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | wb_terms | 0 | PRIMARY | 1 | term_row_id | A | 1682161570 | NULL | NULL | | BTREE | | | | wb_terms | 1 | wb_terms_entity_id | 1 | term_entity_id | A | 98950680 | NULL | NULL | | BTREE | | | | wb_terms | 1 | wb_terms_text | 1 | term_text | A | 240308795 | NULL | NULL | | BTREE | | | | wb_terms | 1 | wb_terms_search_key | 1 | term_search_key | A | 64698521 | NULL | NULL | | BTREE | | | | wb_terms | 1 | term_full_entity | 1 | term_full_entity_id | A | 112144104 | NULL | NULL | YES | BTREE | | | | wb_terms | 1 | term_search_full | 1 | term_language | A | 7067905 | NULL | NULL | | BTREE | | | | wb_terms | 1 | term_search_full | 2 | term_full_entity_id | A | 1682161570 | NULL | NULL | YES | BTREE | | | | wb_terms | 1 | term_search_full | 3 | term_type | A | 1682161570 | NULL | NULL | | BTREE | | | | wb_terms | 1 | term_search_full | 4 | term_search_key | A | 1682161570 | 16 | NULL | | BTREE | | | | wb_terms | 1 | tmp1 | 1 | term_language | A | 6184417 | NULL | NULL | | BTREE | | | | wb_terms | 1 | tmp1 | 2 | term_type | A | 6979923 | NULL | NULL | | BTREE | | | | wb_terms | 1 | tmp1 | 3 | term_entity_type | A | 8048619 | NULL | NULL | | BTREE | | | | wb_terms | 1 | tmp1 | 4 | term_search_key | A | 560720523 | NULL | NULL | | BTREE | | | +----------+------------+---------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 13 rows in set (0.00 sec)
The create statement should be as simple as:
CREATE INDEX `tmp1` ON wb_terms (`term_language`,`term_type`,`term_entity_type`,`term_search_key`) COMMENT 'term search index (in use)';