Thecategorylinks 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 thecategory namedTitle (for which an associated page may or may not exist).Links beginning with a colon, like[[:Category:Title]], are not stored in thecategorylinks table, but are handled as normalinternal 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 ofcl_from andcl_to (for when an article is edited)
• The concatenation ofcl_to,cl_type,cl_sortkey, andcl_from (for showing articles in order)
• The concatenation ofcl_to andcl_timestamp
• cl_collation

Stores thepage.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 ofcl_sortkey.Needed mostly so thatcl_sortkey can be easily updated in certain situations without re-parsing the entire page.More recently added values are valid UTF-8 (seegerrit:449280).

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

Foreign key tocollation.collation_id.

Foreign key tolinktarget.lt_id.

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

Storesthe name of the desired category in thepage_title format (that is, with_ andexcluding 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          | NO   | PRI | 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              |                             |+-------------------+------------------------------+------+-----+-------------------+-----------------------------+

Warning:

Starting with version 1.17, the index on (cl_to,cl_sortkey) is no more, and replaced with one on (cl_to,cl_type,cl_sortkey,cl_from). Extensions that directly query thecategorylinks table to get a list of pages in sorted order need to make sure they are using the new index, or the query may become very inefficient

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

• ExampleThe page list of some category.

• MediaWiki database tables
• Category