Manual:Categorylinks table

The categorylinks table stores entries corresponding to links of the form or, which when placed anywhere on a page places that page into the category named Title (for which an associated page may or may not exist). Links beginning with a colon, like, are not stored in the categorylinks table, but are handled as normal internal links (in this case leading to the page ). The editable parts of category pages are stored like other pages.

There are four indexes which help improve performance:


 * The concatenation of cl_from and cl_to (for when an article is edited)
 * The concatenation of cl_to, cl_type , cl_sortkey , and cl_from (for showing articles in order)
 * The concatenation of cl_to and cl_timestamp
 * cl_collation

cl_from
Stores the page. of the article where the link was placed.

cl_to
Stores the name of the desired category in the format (that is, with  and excluding namespace prefix).

cl_sortkey
Stores the title by which the page should be sorted in a category list. This is the binary sortkey, that depending on may or may not be readable by a human (but should sort in correct order when comparing as a byte string), and is not valid UTF-8 whenever the database truncates the sortkey in the middle of a multi-byte sequence.

cl_timestamp
Stores the time at which that link was last updated in the table.

cl_sortkey_prefix
This is either the empty string if a page is using the default sortkey (aka the sortkey is unspecified). Otherwise it is the human readable version of cl_sortkey. Needed mostly so that cl_sortkey can be easily updated in certain situations without re-parsing the entire page. More recently added values are valid UTF-8 (see change 449280 on Gerrit).

cl_collation
What collation is in use. Used so that if the collation changes, the script knows what rows need to be fixed in db.

cl_type
What type of page is this (,  (subcategory) or  (normal page)). Used so that the different sections on a category page can be paged independently in an efficient manner.

Schema summary
+---+--+--+-+---+-+ +---+--+--+-+---+-+ +---+--+--+-+---+-+
 * Field            | Type                         | Null | Key | Default           | Extra                       |
 * cl_from          | int(10) unsigned             | NO   | PRI | 0                 |                             |
 * cl_to            | varbinary(255)               | NO   | PRI |                   |                             |
 * cl_sortkey       | varbinary(230)               | NO   |     |                   |                             |
 * cl_sortkey_prefix | varbinary(255)              | NO   |     |                   |                             |
 * cl_timestamp     | timestamp                    | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
 * cl_collation     | varbinary(32)                | NO   | MUL |                   |                             |
 * cl_type          | enum('page','subcat','file') | NO   |     | page              |                             |

+--+--+--+-+---+-+ +--+--+--+-+---+-+ +--+--+--+-+---+-+
 * Field       | Type             | Null | Key | Default           | Extra                       |
 * cl_from     | int(10) unsigned | NO   | PRI | 0                 |                             |
 * cl_to       | varbinary(255)   | NO   | PRI |                   |                             |
 * cl_sortkey  | varbinary(70)    | NO   |     |                   |                             |
 * cl_timestamp | timestamp       | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |

+--+-+--+-+---+-+ +--+-+--+-+---+-+ +--+-+--+-+---+-+
 * Field       | Type            | Null | Key | Default           | Extra                       |
 * cl_from     | int(8) unsigned | NO   | PRI | 0                 |                             |
 * cl_to       | varchar(255)    | NO   | PRI |                   |                             |
 * cl_sortkey  | varchar(86)     | NO   |     |                   |                             |
 * cl_timestamp | timestamp      | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |

+--+-+--+-+---+-+ +--+-+--+-+---+-+ +--+-+--+-+---+-+
 * Field       | Type            | Null | Key | Default           | Extra                       |
 * cl_from     | int(8) unsigned | NO   | PRI | 0                 |                             |
 * cl_to       | varchar(255)    | NO   | PRI |                   |                             |
 * cl_sortkey  | varchar(255)    | NO   |     |                   |                             |
 * cl_timestamp | timestamp      | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |

Indexes
 +---++--+--+--+---+-+--++--++-+---+ +---++--+--+--+---+-+--++--++-+---+ +---++--+--+--+---+-+--++--++-+---+
 * Table        | Non_unique | Key_name         | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
 * categorylinks |         0 | PRIMARY          |            1 | cl_from      | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
 * categorylinks |         0 | PRIMARY          |            2 | cl_to        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
 * categorylinks |         1 | cl_sortkey       |            1 | cl_to        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
 * categorylinks |         1 | cl_sortkey       |            2 | cl_type      | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
 * categorylinks |         1 | cl_sortkey       |            3 | cl_sortkey   | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
 * categorylinks |         1 | cl_sortkey       |            4 | cl_from      | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
 * categorylinks |         1 | cl_timestamp     |            1 | cl_to        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
 * categorylinks |         1 | cl_timestamp     |            2 | cl_timestamp | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
 * categorylinks |         1 | cl_collation_ext |            1 | cl_collation | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
 * categorylinks |         1 | cl_collation_ext |            2 | cl_to        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
 * categorylinks |         1 | cl_collation_ext |            3 | cl_type      | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
 * categorylinks |         1 | cl_collation_ext |            4 | cl_from      | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |