Manual:categorylinks table

The categorylinks table stores entries corresponding to links of the form [[Category:Title]] or [[Category:Title|sortkey]], 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 [[:Category:Title]], are not stored in the categorylinks table, but are handled as normal internal links (in this case leading to the page Category:Title). 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

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

Stores the title by which the page should be sorted in a category list. This is the binary sortkey, that depending on $wgCategoryCollation 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.

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

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 the first part 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 gerrit:449280).

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

Foreign key to collation.collation_id.

Foreign key to linktarget.lt_id.

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

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

DESCRIBEcategorylinks;

+-------------------+------------------------------+------+-----+---------------------+-------------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+------------------------------+------+-----+---------------------+-------------------------------+
| cl_from | int(10) unsigned | NO | PRI | 0 | |
| cl_sortkey | varbinary(230) | NO | | | |
| cl_sortkey_prefix | varbinary(255) | NO | | | |
| cl_timestamp | timestamp | NO | | current_timestamp() | on update current_timestamp() |
| cl_type | enum('page','subcat','file') | NO | | page | |
| cl_collation_id | smallint(5) unsigned | NO | | 0 | |
| cl_target_id | bigint(20) unsigned | YES | MUL | NULL | |
+-------------------+------------------------------+------+-----+---------------------+-------------------------------+

DESCRIBEcategorylinks;

+-------------------+------------------------------+------+-----+---------------------+-------------------------------+
| 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 | | | |
| cl_type | enum('page','subcat','file') | NO | | page | |
| cl_collation_id | smallint(5) unsigned | NO | | 0 | |
| cl_target_id | bigint(20) unsigned | YES | MUL | NULL | |
+-------------------+------------------------------+------+-----+---------------------+-------------------------------+

DESCRIBEcategorylinks;

+-------------------+------------------------------+------+-----+-------------------+-----------------------------+
| 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 | |
+-------------------+------------------------------+------+-----+-------------------+-----------------------------+

DESCRIBEcategorylinks;

+--------------+------------------+------+-----+-------------------+-----------------------------+
| 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 |
+--------------+------------------+------+-----+-------------------+-----------------------------+

DESCRIBEcategorylinks;

+--------------+-----------------+------+-----+-------------------+-----------------------------+
| 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 |
+--------------+-----------------+------+-----+-------------------+-----------------------------+

DESCRIBEcategorylinks;

+--------------+-----------------+------+-----+-------------------+-----------------------------+
| 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 |
+--------------+-----------------+------+-----+-------------------+-----------------------------+

SHOWINDEXINcategorylinks;

+---------------+------------+---------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| 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_sortkey_id | 1 | cl_target_id | A | 0 | NULL | NULL | YES | BTREE | | |
| categorylinks | 1 | cl_sortkey_id | 2 | cl_type | A | 0 | NULL | NULL | | BTREE | | |
| categorylinks | 1 | cl_sortkey_id | 3 | cl_sortkey | A | 0 | NULL | NULL | | BTREE | | |
| categorylinks | 1 | cl_sortkey_id | 4 | cl_from | A | 0 | NULL | NULL | | BTREE | | |
+---------------+------------+---------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

• API:Categorymembers

• Example The page list of some category.

• MediaWiki database tables
• Category