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
Fields [edit]
cl_from [edit]
Stores the page.page_id of the article where the link was placed.
Stores the name (excluding namespace prefix) of the desired category. Spaces are replaced by underscores (_)
cl_sortkey [edit]
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)
cl_timestamp [edit]
Stores the time at which that link was last updated in the table.
cl_sortkey_prefix [edit]
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.
cl_collation [edit]
| MediaWiki version: |
≥ 1.17 |
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.
cl_type [edit]
What type of article is this (file, subcategory (subcat) or normal page). Used so that the different sections on a category page can be paged independently in an efficient manner.
Versions [edit]
| MediaWiki versions: |
1.19 – 1.20 |
mysql> describe categorylinks;
+-------------------+------------------------------+------+-----+-------------------+-----------------------------+
| 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(230) | NO | | | |
| cl_timestamp | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| cl_sortkey_prefix | varbinary(255) | NO | | | |
| cl_collation | tinyint(4) | NO | MUL | 0 | |
| 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 the categorylinks 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 |
| MediaWiki versions: |
1.10 – 1.15 |
+--------------+-----------------+------+-----+-------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-----------------+------+-----+-------------------+-------+
| cl_from | int(10) unsigned| NO | PRI | 0 | |
| cl_to | varchar(255) | NO | PRI | NULL | |
| cl_sortkey | varchar(70) | NO | | NULL | |
| cl_timestamp | timestamp | YES | | CURRENT_TIMESTAMP | |
+--------------+-----------------+------+-----+-------------------+-------+
| MediaWiki versions: |
1.5 – 1.9 |
+--------------+-----------------+------+-----+-------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-----------------+------+-----+-------------------+-------+
| cl_from | int(8) unsigned | NO | PRI | 0 | |
| cl_to | varchar(255) | NO | PRI | NULL | |
| cl_sortkey | varchar(86) | NO | | NULL | |
| cl_timestamp | timestamp | YES | | CURRENT_TIMESTAMP | |
+--------------+-----------------+------+-----+-------------------+-------+
+--------------+-----------------+------+-----+-------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-----------------+------+-----+-------------------+-------+
| cl_from | int(8) unsigned | NO | PRI | 0 | |
| cl_to | varchar(255) | NO | PRI | NULL | |
| cl_sortkey | varchar(255) | NO | | NULL | |
| cl_timestamp | timestamp | YES | | CURRENT_TIMESTAMP | |
+--------------+-----------------+------+-----+-------------------+-------+
Warning:
