Manual:categorylinks table

From MediaWiki.org
Jump to: navigation, search
Manual:ContentsManual:Contents MediaWiki database layoutManual:Database layout 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

Fields[edit]

cl_from[edit]

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

cl_to[edit]

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

Schema summary[edit]

MediaWiki version: 1.17

DESCRIBE categorylinks;

+-------------------+------------------------------+------+---------+-------------------+-----------------------------+
| Field             | Type                         | Null | Key     | Default           | Extra                       |
+-------------------+------------------------------+------+---------+-------------------+-----------------------------+
| cl_from           | int(10) unsigned             | NO   | UNI/PRI | 0                 |                             |
| cl_to             | varchar(255) binary          | NO   | PRI     | NULL              |                             |
| cl_sortkey        | varbinary(230)               | NO   |         | NULL              |                             |
| cl_sortkey_prefix | varchar(255) binary          | NO   |         | NULL              |                             |
| cl_timestamp      | timestamp                    | NO   |         | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| cl_collation      | varbinary(32)                | NO   | MUL     | NULL              |                             |
| cl_type           | enum('page','subcat','file') | NO   |         | 'page'            |                             |
+-------------------+------------------------------+------+---------+-------------------+-----------------------------+
Warning 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.16

DESCRIBE categorylinks;

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


MediaWiki versions: 1.5 – 1.9

DESCRIBE categorylinks;

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


MediaWiki versions: 1.3 – 1.4

DESCRIBE categorylinks;

+--------------+-----------------+------+---------+-------------------+-----------------------------+
| Field        | Type            | Null | Key     | Default           | Extra                       |
+--------------+-----------------+------+---------+-------------------+-----------------------------+
| cl_from      | int(8) unsigned | NO   | UNI/PRI | 0                 |                             |
| cl_to        | varbinary(255)  | NO   | PRI     | NULL              |                             |
| cl_sortkey   | varbinary(255)  | NO   |         | NULL              |                             |
| cl_timestamp | timestamp       | NO   |         | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+--------------+-----------------+------+---------+-------------------+-----------------------------+
DatabasesManual:Database layout Engines: MySQL/MariaDBManual:MySQLOracleManual:OraclePostgreSQLManual:PostgreSQLSQLiteManual:SQLiteSQL ServerManual:Microsoft SQL Server

Technical documentation: SchemaManual:Database layout (tablesCategory:MediaWiki database tables) – API property associationsAPI:Database field and API property associationsField prefixesDatabase field prefixesPrimary key storage in other fieldsManual:Primary key storage in other fieldsWikimedia extension tablesCategory:Wikimedia extension database tables
Configuration: SettingsManual:Configuration_settings#Database_settingsSharingManual:Shared database
Development: AccessManual:Database accessOptimizationDatabase optimizationPolicyDevelopment policy#Database_patchesUpdaterManual:DatabaseUpdater.phpExtension schema updatesManual:Hooks/LoadExtensionSchemaUpdatesPatch fileManual:SQL patch file

Core tables: archivebot_passwordscategorycategorylinkschange_tagconfigexternallinksfilearchivehitcounterimageimagelinksinterwikiiwlinksipblocksjobl10n_cachelanglinkslogginglog_searchmsg_resourcemsg_resource_linksmodule_depsobjectcacheoldimagepagepagelinkspage_propspage_restrictionsprotected_titlesquerycachequerycachetwoquerycache_inforecentchangesredirectrevisionsearchindexsitessite_identifiers - site_statstag_summarytemplatelinkstexttranscacheupdateloguploadstashuseruser_former_groupsuser_groupsuser_newtalkuser_propertiestag_summaryvalid_tagwatchlist
Language:Project:Language policy English  • 日本語 • polski