This is a RFC for normalizing target columns of *links tables (These tables to be precise: [[https://www.mediawiki.org/wiki/Manual:Imagelinks_table|imagelinks]], [[https://www.mediawiki.org/wiki/Manual:Pagelinks_table|pagelinks]], [[https://www.mediawiki.org/wiki/Manual:Templatelinks_table|templatelinks]], [[https://www.mediawiki.org/wiki/Manual:Categorylinks_table|categorylinks]]).
**Problem**
These *links tables are among largest tables in mediawiki installations. Here's an example from commonswiki:
```
wikiadmin@10.64.16.102(commonswiki)> SELECT table_schema, table_name AS `Table`, round(((data_length + index_length) / 1024 / 1024 / 1024), 2) `Size in GB` FROM information_schema.TABLES where table_name like '%links' order by (data_length + index_length) desc;
+-----------------+------------------+------------+
| table_schema | Table | Size in GB |
+-----------------+------------------+------------+
| commonswiki | templatelinks | 301.45 |
| commonswiki | externallinks | 179.16 |
| commonswiki | categorylinks | 175.10 |
| commonswiki | globalimagelinks | 136.36 |
| commonswiki | pagelinks | 84.96 |
| commonswiki | imagelinks | 55.47 |
| commonswiki | iwlinks | 42.81 |
| commonswiki | langlinks | 2.74 |
| testcommonswiki | pagelinks | 0.00 |
| testcommonswiki | templatelinks | 0.00 |
| testcommonswiki | externallinks | 0.00 |
| testcommonswiki | iwlinks | 0.00 |
| testcommonswiki | imagelinks | 0.00 |
| testcommonswiki | categorylinks | 0.00 |
| testcommonswiki | globalimagelinks | 0.00 |
| testcommonswiki | langlinks | 0.00 |
+-----------------+------------------+------------+
16 rows in set (0.01 sec)
```
In total they are responsible for 0.8TB of data in Wikimedia Commons. Most of these data is redundant because target fields are repeated over and over again, for example "License_template_tag" as value of templatelinks.tl_target has been repeated 53 million times. This can be easily normalized through page table but the problem is that the actual content in mediawiki binds to the text. You use {{License template tag}} and notthe page id of Template:License template tag in commonswiki. That has several consequences: 1- You can link to non-existing page and store those links (which empowers special pages of "Most wanted categories" and "Most wanted pages") 2- If a page moves, the links stays to the old target which either gets redirected, repurposed to a completely different page or fixed by a bot. This can be a hassle, we have bots to fix moved categories but fixing that is outside of scope of this RFC.
**Proposed solution**
I propose to add a new table with this structure:
```lang=mysql
CREATE TABLE /*_*/title (
title_id BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
title_namespace INT NOT NULL,
title_title VARCHAR(255) BINARY NOT NULL, -- Naming is hard
) /*$wgDBTableOptions*/;
CREATE UNIQUE INDEX /*i*/title_title ON /*_*/title (title_namespace, title_title);
```
(`ts` prefix used to be used in `tag_summary` table but it doesn't exist anymore, I killed it with my bare hands)
Then a lookup service would be introduced similar to NameTableStore but with a different caching strategy (caching only the most read lookups and not all in memcached) to provide a lookup from string to id and vice versa.
Then for each of the mentioned *links table:
- We add a new column to the table
- Turn on the migration stage on "write both, read old"
- Run the maintenance script to populate the new column
- Turn the migration to "write both, read new"
- After a period, reading only from the normalized column and dropping the non-normalized column.
(Repeating the whole procedure for each table, I did something similar but more complex with change_tags table)
That would save a lot of space and addressed significant scalability issues for Wikimedia Commons and English Wikipedia (these tables are around 60 GB in wikidatawiki, rather big but not the most pressing storage issue there).
Disclaimer: I do this on my volunteer capacity, this is not owned by WMDE or WMF. I would be very happy if any team would like to help with this.