User:Ladsgroup/Test

actor

The "actor" table associates user names or IP addresses with integers for the benefit of other tables that need to refer to either logged-in or logged-out users. If something can only ever be done by logged-in users, it can refer to the user table directly.

Primary Key: actor_id

Columns

edit
Name Type Nullable Default Extra options Description
actor_id bigint No No default Unsigned Unique ID to identify each actor
actor_user integer Yes No default Unsigned Key to user.user_id, or NULL for anonymous edits
actor_name binary No No default Length: 255 Text username or IP address

Indexes

edit
Name Columns Unique Description
actor_user actor_user Yes
actor_name actor_name Yes

bot_passwords

edit

This table contains a user's bot passwords: passwords that allow access to the account via the API with limited rights.

Primary Key: bp_user, bp_app_id

Columns

edit
Name Type Nullable Default Extra options Description
bp_user integer No No default Unsigned User ID obtained from CentralIdLookup.
bp_app_id binary No No default Length: 32 Application identifier.
bp_password blob No No default Length: 255 Password hashes, like user.user_password.
bp_token binary No "" Length: 32 Like user.user_token
bp_restrictions blob No No default Length: 65535 JSON blob for MWRestrictions
bp_grants blob No No default Length: 65535 Grants allowed to the account when authenticated with this bot-password

category

edit

Track all existing categories. Something is a category if 1) it has an entry somewhere in categorylinks, or 2) it has a description page. Categories might not have corresponding pages, so they need to be tracked separately. The numbers of member pages (including categories and media), subcategories, and Image: namespace members, respectively are included in this table too. These are signed to make underflow more obvious. We make the first number include the second two for better sorting: subtracting for display is easy, adding for ordering is not.

Primary Key: cat_id

Columns

edit
Name Type Nullable Default Extra options Description
cat_id integer No No default Unsigned Primary key
cat_title binary No No default Length: 255 Name of the category, in the same form as page_title (with underscores). If there is a category page corresponding to this category, by definition, it has this name (in the Category namespace).
cat_pages integer No 0
cat_subcats integer No 0
cat_files integer No 0

Indexes

edit
Name Columns Unique Description
cat_title cat_title Yes
cat_pages cat_pages No For Special:Mostlinkedcategories
edit

Track category inclusions *used inline* This tracks a single level of category membership

Primary Key: cl_from, cl_to

Columns

edit
Name Type Nullable Default Extra options Description
cl_from integer No 0 Unsigned Key to page_id of the page defined as a category member.
cl_to binary No "" Length: 255 Name of the category. This is also the page_title of the category's description page; all such pages are in namespace 14 (NS_CATEGORY).
cl_sortkey binary No "" Length: 230 A binary string obtained by applying a sortkey generation algorithm (Collation::getSortKey()) to page_title, or cl_sortkey_prefix . "\n" page_title if cl_sortkey_prefix is nonempty.
cl_sortkey_prefix binary No "" Length: 255 A prefix for the raw sortkey manually specified by the user, either via or Template:Defaultsort:prefix. If nonempty, it's concatenated with a line break followed by the page title before the sortkey conversion algorithm is run. We store this so that we can update collations without reparsing all pages. Note: If you change the length of this field, you also need to change code in LinksUpdate.php. See T27254.
cl_timestamp datetimetz No No default This isn't really used at present. Provided for an optional sorting method by approximate addition time.
cl_collation binary No "" Length: 32 Stores $wgCategoryCollation at the time cl_sortkey was generated. This can be used to install new collation versions, tracking which rows are not yet updated. '' means no collation, this is a legacy row that needs to be updated by updateCollation.php. In the future, it might be possible to specify different collations per category.
cl_type mwenum No page Stores whether cl_from is a category, file, or other page, so we can paginate the three categories separately. This only has to be updated when moving pages into or out of the category namespace, since file pages cannot be moved to other namespaces, nor can non-files be moved into the file namespace.

Indexes

edit
Name Columns Unique Description
cl_sortkey cl_to, cl_type, cl_sortkey, cl_from No We always sort within a given category, and within a given type. FIXME: Formerly this index didn't cover cl_type (since that didn't exist), so old callers won't be using an index: fix this?
cl_timestamp cl_to, cl_timestamp No Used by the API (and some extensions)
cl_collation_ext cl_collation, cl_to, cl_type, cl_from No Used when updating collation (e.g. updateCollation.php)

change_tag

edit

A table to track tags for revisions, logs and recent changes

Primary Key: ct_id

Columns

edit
Name Type Nullable Default Extra options Description
ct_id integer No No default Unsigned
ct_rc_id integer Yes NULL Unsigned RCID for the change
ct_log_id integer Yes NULL Unsigned LOGID for the change
ct_rev_id integer Yes NULL Unsigned REVID for the change
ct_params blob Yes NULL Length: 65530 Parameters for the tag; used by some extensions
ct_tag_id integer No No default Unsigned Foreign key to change_tag_def row

Indexes

edit
Name Columns Unique Description
change_tag_rc_tag_id ct_rc_id, ct_tag_id Yes
change_tag_log_tag_id ct_log_id, ct_tag_id Yes
change_tag_rev_tag_id ct_rev_id, ct_tag_id Yes
change_tag_tag_id_id ct_tag_id, ct_rc_id, ct_rev_id, ct_log_id No Covering index, so we can pull all the info only out of the index.

change_tag_def

edit

Table defining tag names for IDs. Also stores hit counts to avoid expensive queries on change_tag

Primary Key: ctd_id

Columns

edit
Name Type Nullable Default Extra options Description
ctd_id integer No No default Unsigned Numerical ID of the tag (ct_tag_id refers to this)
ctd_name binary No No default Length: 255 Symbolic name of the tag (what would previously be put in ct_tag)
ctd_user_defined mwtinyint No No default Length: 1 Whether this tag was defined manually by a privileged user using Special:Tags
ctd_count bigint No 0 Unsigned Number of times this tag was used

Indexes

edit
Name Columns Unique Description
ctd_name ctd_name Yes
ctd_count ctd_count No
ctd_user_defined ctd_user_defined No

comment

edit

Edits, blocks, and other actions typically have a textual comment describing the action. They are stored here to reduce the size of the main tables, and to allow for deduplication. Deduplication is currently best-effort to avoid locking on inserts that would be required for strict deduplication. There MAY be multiple rows with the same comment_text and comment_data.

Primary Key: comment_id

Columns

edit
Name Type Nullable Default Extra options Description
comment_id bigint No No default Unsigned Unique ID to identify each comment
comment_hash integer No No default Hash of comment_text and comment_data, for deduplication
comment_text blob No No default Length: 65535 Text comment summarizing the change. This text is shown in the history and other changes lists, rendered in a subset of wiki markup by Linker::formatComment(). Size limits are enforced at the application level, and should take care to crop UTF-8 strings appropriately.
comment_data blob Yes No default Length: 65535 JSON data, intended for localizing auto-generated comments. This holds structured data that is intended to be used to provide localized versions of automatically-generated comments. When not empty, comment_text should be the generated comment localized using the wiki's content language.

Indexes

edit
Name Columns Unique Description
comment_hash comment_hash No

content

edit

The content table represents content objects. It's primary purpose is to provide the necessary meta-data for loading and interpreting a serialized data blob to create a content object.

Primary Key: content_id

Columns

edit
Name Type Nullable Default Extra options Description
content_id bigint No No default Unsigned ID of the content object
content_size integer No No default Unsigned Nominal size of the content object (not necessarily of the serialized blob)
content_sha1 binary No No default Length: 32 Nominal hash of the content object (not necessarily of the serialized blob)
content_model smallint No No default Unsigned reference to model_id. Note the content format isn't specified; it should be assumed to be in the default format for the model unless auto-detected otherwise.
content_address binary No No default Length: 255 URL-like address of the content blob

content_models

edit

Normalization table for content model names

Primary Key: model_id

Columns

edit
Name Type Nullable Default Extra options Description
model_id integer No No default
model_name binary No No default Length: 64

Indexes

edit
Name Columns Unique Description
model_name model_name Yes Index for looking up the internal ID of a model
edit

Track links to external URLs

Primary Key: el_id

Columns

edit
Name Type Nullable Default Extra options Description
el_id integer No No default Unsigned
el_from integer No 0 Unsigned page_id of the referring page
el_to blob No No default Length: 65530 The external link
el_index blob No No default Length: 65530 In the case of HTTP URLs, this is the URL with any username or password removed, and with the labels in the hostname reversed and converted to lower case which will allow faster searching for all pages with WHERE clause. Note: If PHP's intl extension is enabled/disabled, maintenance/refreshExternallinksIndex.php needs to be run to refresh this field
el_index_60 binary No No default Length: 60 'el_index' truncated to 60 bytes to allow for sortable queries that aren't supported by a partial index

Indexes

edit
Name Columns Unique Description
el_from el_from, el_to No
el_to el_to, el_from No
el_index el_index No
el_index_60 el_index_60, el_id No
el_from_index_60 el_from, el_index_60, el_id No

filearchive

edit

Record of deleted file data

Primary Key: fa_id

Columns

edit
Name Type Nullable Default Extra options Description
fa_id integer No No default Unique row id
fa_name binary No "" Length: 255 Original base filename; key to image.img_name, page.page_title, etc
fa_archive_name binary Yes "" Length: 255 Filename of archived file, if an old revision
fa_storage_group binary Yes No default Length: 16 Which storage bin (directory tree or object store) the file data is stored in. Should be 'deleted' for files that have been deleted; any other bin is not yet in use.
fa_storage_key binary Yes "" Length: 64 SHA-1 of the file contents plus extension, used as a key for storage. eg 8f8a562add37052a1848ff7771a2c515db94baa9.jpg. If NULL, the file was missing at deletion time or has been purged from the archival storage.
fa_deleted_user integer Yes No default
fa_deleted_timestamp mwtimestamp Yes No default Length: 14
fa_deleted_reason_id bigint No No default Unsigned
fa_size integer Yes 0 Unsigned
fa_width integer Yes 0
fa_height integer Yes 0
fa_metadata blob Yes No default Length: 16777215
fa_bits integer Yes 0
fa_media_type mwenum Yes NULL
fa_major_mime mwenum Yes unknown
fa_minor_mime binary Yes unknown Length: 100
fa_description_id bigint No No default Unsigned
fa_actor bigint No No default Unsigned
fa_timestamp mwtimestamp Yes No default Length: 14
fa_deleted mwtinyint No 0 Unsigned Visibility of deleted revisions, bitfield
fa_sha1 binary No "" Length: 32 sha1 hash of file content

Indexes

edit
Name Columns Unique Description
fa_name fa_name, fa_timestamp No pick out by image name
fa_storage_group fa_storage_group, fa_storage_key No pick out dupe files
fa_deleted_timestamp fa_deleted_timestamp No sort by deletion time
fa_actor_timestamp fa_actor, fa_timestamp No sort by uploader
fa_sha1 fa_sha1 No find file by sha1, 10 bytes will be enough for hashes to be indexed

image

edit

Uploaded images and other files.

Primary Key: img_name

Columns

edit
Name Type Nullable Default Extra options Description
img_name binary No "" Length: 255 Filename. This is also the title of the associated description page, which will be in namespace 6 (NS_FILE).
img_size integer No 0 Unsigned File size in bytes.
img_width integer No 0 For images, width in pixels.
img_height integer No 0 For images, height in pixels.
img_metadata blob No No default Length: 16777215 Extracted Exif metadata stored as a serialized PHP array.
img_bits integer No 0 For images, bits per pixel if known.
img_media_type mwenum Yes NULL Media type as defined by the MEDIATYPE_xxx constants
img_major_mime mwenum No unknown major part of a MIME media type as defined by IANA see https://www.iana.org/assignments/media-types/ for "chemical" cf. http://dx.doi.org/10.1021/ci9803233 by the ACS
img_minor_mime binary No unknown Length: 100 minor part of a MIME media type as defined by IANA the minor parts are not required to adhere to any standard but should be consistent throughout the database see https://www.iana.org/assignments/media-types/
img_description_id bigint No No default Unsigned Foreign key to comment table, which contains the description field as entered by the uploader. This is displayed in image upload history and logs.
img_actor bigint No No default Unsigned actor_id of the uploader.
img_timestamp mwtimestamp No No default Length: 14 Time of the upload.
img_sha1 binary No "" Length: 32 SHA-1 content hash in base-36

Indexes

edit
Name Columns Unique Description
img_actor_timestamp img_actor, img_timestamp No Used by Special:Newimages and ApiQueryAllImages
img_size img_size No Used by Special:ListFiles for sort-by-size
img_timestamp img_timestamp No Used by Special:Newimages and Special:ListFiles
img_sha1 img_sha1 No Used in API and duplicate search
img_media_mime img_media_type, img_major_mime, img_minor_mime No Used to get media of one type
edit

Track links to images *used inline* We don't distinguish live from broken links here, so they do not need to be changed on upload/removal.

Primary Key: il_from, il_to

Columns

edit
Name Type Nullable Default Extra options Description
il_from integer No 0 Unsigned Key to page_id of the page containing the image / media link.
il_from_namespace integer No 0 Namespace for this page
il_to binary No "" Length: 255 Filename of target image. This is also the page_title of the file's description page; all such pages are in namespace 6 (NS_FILE).

Indexes

edit
Name Columns Unique Description
il_to il_to, il_from No Reverse index, for Special:Whatlinkshere and file description page local usage
il_backlinks_namespace il_from_namespace, il_to, il_from No Index for Special:Whatlinkshere with namespace filter

interwiki

edit

Recognized interwiki link prefixes

Primary Key: iw_prefix

Columns

edit
Name Type Nullable Default Extra options Description
iw_prefix string No No default Length: 32 The interwiki prefix, (e.g. "Meatball", or the language prefix "de")
iw_url blob No No default Length: 65530 The URL of the wiki, with "$1" as a placeholder for an article name. Any spaces in the name will be transformed to underscores before insertion.
iw_api blob No No default Length: 65530 The URL of the file api.php
iw_wikiid string No No default Length: 64 The name of the database (for a connection to be established with LBFactory::getMainLB( 'wikiid' ))
iw_local mwtinyint No No default Length: 1 A boolean value indicating whether the wiki is in this project (used, for example, to detect redirect loops)
iw_trans mwtinyint No 0 Boolean value indicating whether interwiki transclusions are allowed.

ip_changes

edit

Every time an edit by a logged out user is saved, a row is created in ip_changes. This stores the IP as a hex representation so that we can more easily find edits within an IP range.

Primary Key: ipc_rev_id

Columns

edit
Name Type Nullable Default Extra options Description
ipc_rev_id integer No 0 Unsigned Foreign key to the revision table, also serves as the unique primary key
ipc_rev_timestamp mwtimestamp No No default The timestamp of the revision
ipc_hex binary No "" Length: 35 Hex representation of the IP address, as returned by Wikimedia\IPUtils::toHex() For IPv4 it will resemble: ABCD1234 For IPv6: v6-ABCD1234000000000000000000000000 BETWEEN is then used to identify revisions within a given range

Indexes

edit
Name Columns Unique Description
ipc_rev_timestamp ipc_rev_timestamp No
ipc_hex_time ipc_hex, ipc_rev_timestamp No

ipblocks

edit

Blocks against user accounts, IP addresses and IP ranges.

Primary Key: ipb_id

Columns

edit
Name Type Nullable Default Extra options Description
ipb_id integer No No default Primary key, introduced for privacy.
ipb_address blob No No default Length: 255 Blocked IP address in dotted-quad form or user name.
ipb_user integer No 0 Unsigned Blocked user ID or 0 for IP blocks.
ipb_by_actor bigint No No default Unsigned Actor who made the block.
ipb_reason_id bigint No No default Unsigned Key to comment_id. Text comment made by blocker.
ipb_timestamp mwtimestamp No No default Creation (or refresh) date in standard YMDHMS form. IP blocks expire automatically.
ipb_auto mwtinyint No 0 Length: 1 Indicates that the IP address was banned because a banned user accessed a page through it. If this is 1, ipb_address will be hidden, and the block identified by block ID number.
ipb_anon_only mwtinyint No 0 Length: 1 If set to 1, block applies only to logged-out users
ipb_create_account mwtinyint No 1 Length: 1 Block prevents account creation from matching IP addresses
ipb_enable_autoblock mwtinyint No 1 Length: 1 Block triggers autoblocks
ipb_expiry mwtimestamp No No default Time at which the block will expire. May be "infinity"
ipb_range_start blob No No default Length: 255 Start of an address range, in hexadecimal size chosen to allow IPv6. FIXME: this field were originally blank for single-IP blocks, but now it's populated. No migration was ever done. It should be fixed to be blank again for such blocks (T51504).
ipb_range_end blob No No default Length: 255 End of an address range, in hexadecimal size chosen to allow IPv6. FIXME: this field were originally blank for single-IP blocks, but now it's populated. No migration was ever done. It should be fixed to be blank again for such blocks (T51504).
ipb_deleted mwtinyint No 0 Length: 1 Flag for entries hidden from users and Sysops
ipb_block_email mwtinyint No 0 Length: 1 Block prevents user from accessing Special:Emailuser
ipb_allow_usertalk mwtinyint No 0 Length: 1 Block allows user to edit their own talk page
ipb_parent_block_id integer Yes NULL ID of the block that caused this block to exist. Autoblocks set this to the original block so that the original block being deleted also deletes the autoblocks
ipb_sitewide mwtinyint No 1 Length: 1 Block user from editing any page on the site (other than their own user talk page).

Indexes

edit
Name Columns Unique Description
ipb_address_unique ipb_address, ipb_user, ipb_auto Yes Unique index to support "user already blocked" messages. Any new options which prevent collisions should be included
ipb_user ipb_user No For querying whether a logged-in user is blocked
ipb_range ipb_range_start, ipb_range_end No For querying whether an IP address is in any range
ipb_timestamp ipb_timestamp No Index for Special:BlockList
ipb_expiry ipb_expiry No Index for table pruning
ipb_parent_block_id ipb_parent_block_id No Index for removing autoblocks when a parent block is removed

ipblocks_restrictions

edit

Partial Block Restrictions

Primary Key: ir_ipb_id, ir_type, ir_value

Columns

edit
Name Type Nullable Default Extra options Description
ir_ipb_id integer No No default The ipb_id from ipblocks
ir_type mwtinyint No No default Length: 4 The restriction type id.
ir_value integer No No default The restriction id that corresponds to the type. Typically a Page ID or a Namespace ID.

Indexes

edit
Name Columns Unique Description
ir_type_value ir_type, ir_value No Index to query restrictions by the page or namespace.
edit

Track inline interwiki links

Primary Key: iwl_from, iwl_prefix, iwl_title

Columns

edit
Name Type Nullable Default Extra options Description
iwl_from integer No 0 Unsigned page_id of the referring page
iwl_prefix binary No "" Length: 20 Interwiki prefix code of the target
iwl_title binary No "" Length: 255 Title of the target, including namespace

Indexes

edit
Name Columns Unique Description
iwl_prefix_title_from iwl_prefix, iwl_title, iwl_from No Index for ApiQueryIWBacklinks
iwl_prefix_from_title iwl_prefix, iwl_from, iwl_title No Index for ApiQueryIWLinks

Jobs performed by parallel apache threads or a command-line daemon

Primary Key: job_id

Columns

edit
Name Type Nullable Default Extra options Description
job_id integer No No default Unsigned
job_cmd binary No "" Length: 60 Command name. Limited to 60 to prevent key length overflow
job_namespace integer No No default Namespace to act on. Should be 0 if the command does not operate on a title
job_title binary No No default Length: 255 Title to act on. Should be '' if the command does not operate on a title
job_timestamp mwtimestamp Yes No default Timestamp of when the job was inserted. NULL for jobs added before addition of the timestamp
job_params blob No No default Length: 16777215 Any other parameters to the command. Stored as a PHP serialized array, or an empty string if there are no parameters
job_random integer No 0 Unsigned Random, non-unique, number used for job acquisition (for lock concurrency)
job_attempts integer No 0 Unsigned The number of times this job has been locked
job_token binary No "" Length: 32 Field that conveys process locks on rows via process UUIDs
job_token_timestamp mwtimestamp Yes No default Timestamp when the job was locked
job_sha1 binary No "" Length: 32 Base 36 SHA1 of the job parameters relevant to detecting duplicates

Indexes

edit
Name Columns Unique Description
job_sha1 job_sha1 No
job_cmd_token job_cmd, job_token, job_random No
job_cmd_token_id job_cmd, job_token, job_id No
job_cmd job_cmd, job_namespace, job_title, job_params No
job_timestamp job_timestamp No

l10n_cache

edit

Table for storing localisation data

Primary Key: lc_lang, lc_key

Columns

edit
Name Type Nullable Default Extra options Description
lc_lang binary No No default Length: 35 Language code
lc_key string No No default Length: 255 Cache key
lc_value blob No No default Length: 16777215 Value
edit

Track interlanguage links.

Primary Key: ll_from, ll_lang

Columns

edit
Name Type Nullable Default Extra options Description
ll_from integer No 0 Unsigned page_id of the referring page
ll_lang binary No "" Length: 35 Language code of the target
ll_title binary No "" Length: 255 Title of the target, including namespace

Indexes

edit
Name Columns Unique Description
ll_lang ll_lang, ll_title No Index for ApiQueryLangbacklinks
edit

Primary Key: ls_field, ls_value, ls_log_id

Columns

edit
Name Type Nullable Default Extra options Description
ls_field binary No No default Length: 32 The type of ID (rev ID, log ID, rev timestamp, username)
ls_value string No No default Length: 255 The value of the ID
ls_log_id integer No 0 Unsigned Key to log_id

Indexes

edit
Name Columns Unique Description
ls_log_id ls_log_id No

logging

edit

Primary Key: log_id

Columns

edit
Name Type Nullable Default Extra options Description
log_id integer No No default Unsigned Log ID, for referring to this specific log entry, probably for deletion and such.
log_type binary No "" Length: 32 Symbolic key for the general log type. The output format will be controlled by the log_action field.
log_action binary No "" Length: 32 Symbolic key for the log action type.
log_timestamp mwtimestamp No 19700101000000
log_actor bigint No No default Unsigned
log_namespace integer No 0 Key to the namespace of the page affected
log_title binary No "" Length: 255 Key to the title of the page affected
log_page integer Yes No default Unsigned Key to the page affected
log_comment_id bigint No No default Unsigned Key to comment_id. Comment summarizing the change.
log_params blob No No default Length: 65530 LF separated list (old system) or serialized PHP array (new system)
log_deleted mwtinyint No 0 Unsigned rev_deleted for logs

Indexes

edit
Name Columns Unique Description
log_type_time log_type, log_timestamp No Special:Log type filter
log_actor_time log_actor, log_timestamp No Special:Log performer filter
log_page_time log_namespace, log_title, log_timestamp No Special:Log title filter, log extract
log_times log_timestamp No Special:Log unfiltered
log_actor_type_time log_actor, log_type, log_timestamp No Special:Log filter by performer and type
log_page_id_time log_page, log_timestamp No Apparently just used for a few maintenance pages (findMissingFiles.php, Flow). Could be removed?
log_type_action log_type, log_action, log_timestamp No Special:Log action filter

module_deps

edit

Table caching which local files a module depends on that aren't registered directly, used for fast retrieval of file dependency. Currently only used for tracking images that CSS depends on

Primary Key: md_module, md_skin

Columns

edit
Name Type Nullable Default Extra options Description
md_module binary No No default Length: 255 Module name
md_skin binary No No default Length: 32 Module context vary (includes skin and language; called "md_skin" for legacy reasons)
md_deps blob No No default Length: 16777215 JSON blob with file dependencies

objectcache

edit

For a few generic cache operations if not using Memcached

Primary Key: keyname

Columns

edit
Name Type Nullable Default Extra options Description
keyname binary No "" Length: 255
value blob Yes No default Length: 16777215
exptime mwtimestamp No No default

Indexes

edit
Name Columns Unique Description
exptime exptime No

oldimage

edit

Previous revisions of uploaded files. Awkwardly, image rows have to be moved into this table at re-upload time.

Columns

edit
Name Type Nullable Default Extra options Description
oi_name binary No "" Length: 255 Base filename: key to image.img_name
oi_archive_name binary No "" Length: 255 Filename of the archived file. This is generally a timestamp and '!' prepended to the base name.
oi_size integer No 0 Unsigned
oi_width integer No 0
oi_height integer No 0
oi_bits integer No 0
oi_description_id bigint No No default Unsigned
oi_actor bigint No No default Unsigned
oi_timestamp mwtimestamp No No default Length: 14
oi_metadata blob No No default Length: 16777215
oi_media_type mwenum Yes NULL
oi_major_mime mwenum No unknown
oi_minor_mime binary No unknown Length: 100
oi_deleted mwtinyint No 0 Unsigned
oi_sha1 binary No "" Length: 32

Indexes

edit
Name Columns Unique Description
oi_actor_timestamp oi_actor, oi_timestamp No
oi_name_timestamp oi_name, oi_timestamp No
oi_name_archive_name oi_name, oi_archive_name No oi_archive_name truncated to 14 to avoid key length overflow
oi_sha1 oi_sha1 No

page_props

edit

Name/value pairs indexed by page_id

Primary Key: pp_page, pp_propname

Columns

edit
Name Type Nullable Default Extra options Description
pp_page integer No No default
pp_propname binary No No default Length: 60
pp_value blob No No default Length: 65530
pp_sortkey float Yes No default

Indexes

edit
Name Columns Unique Description
pp_propname_page pp_propname, pp_page Yes
pp_propname_sortkey_page pp_propname, pp_sortkey, pp_page Yes

page_restrictions

edit

Used for storing page restrictions (i.e. protection levels)

Primary Key: pr_id

Columns

edit
Name Type Nullable Default Extra options Description
pr_id integer No No default Unsigned Field for an ID for this restrictions row (sort-key for Special:ProtectedPages)
pr_page integer No No default Page to apply restrictions to (Foreign Key to page).
pr_type binary No No default Length: 60 The protection type (edit, move, etc)
pr_level binary No No default Length: 60 The protection level (Sysop, autoconfirmed, etc)
pr_cascade mwtinyint No No default
pr_user integer Yes No default Unsigned Field for future support of per-user restriction.
pr_expiry mwtimestamp Yes No default Field for time-limited protection.

Indexes

edit
Name Columns Unique Description
pr_pagetype pr_page, pr_type Yes
pr_typelevel pr_type, pr_level No
pr_level pr_level No
pr_cascade pr_cascade No
edit

Track page-to-page hyperlinks within the wiki. The target page may or may not exist, and due to renames and deletions may refer to different page records as time goes by.

Primary Key: pl_from, pl_namespace, pl_title

Columns

edit
Name Type Nullable Default Extra options Description
pl_from integer No 0 Unsigned Key to the page_id of the page containing the link.
pl_namespace integer No 0
pl_title binary No "" Length: 255
pl_from_namespace integer No 0 Namespace for pl_from page

Indexes

edit
Name Columns Unique Description
pl_namespace pl_namespace, pl_title, pl_from No Reverse index, for Special:Whatlinkshere
pl_backlinks_namespace pl_from_namespace, pl_namespace, pl_title, pl_from No Index for Special:Whatlinkshere with namespace filter

protected_titles

edit

Used for storing nonexistent pages that have been protected

Primary Key: pt_namespace, pt_title

Columns

edit
Name Type Nullable Default Extra options Description
pt_namespace integer No No default
pt_title binary No No default Length: 255
pt_user integer No No default Unsigned
pt_reason_id bigint No No default Unsigned
pt_timestamp mwtimestamp No No default
pt_expiry mwtimestamp No No default
pt_create_perm binary No No default Length: 60

Indexes

edit
Name Columns Unique Description
pt_timestamp pt_timestamp No

querycache

edit

Used for caching expensive grouped queries

Columns

edit
Name Type Nullable Default Extra options Description
qc_type binary No No default Length: 32 A key name, generally the base name of of the special page
qc_value integer No 0 Unsigned Some sort of stored value. Sizes, counts...
qc_namespace integer No 0
qc_title binary No "" Length: 255

Indexes

edit
Name Columns Unique Description
qc_type qc_type, qc_value No

querycache_info

edit

Details of updates to cached special pages

Primary Key: qci_type

Columns

edit
Name Type Nullable Default Extra options Description
qci_type binary No "" Length: 32 Special page name. Corresponds to a qc_type value
qci_timestamp mwtimestamp No 19700101000000 Length: 14 Timestamp of last update

querycachetwo

edit

Used for caching expensive grouped queries that need two links (for example double-redirects)

Columns

edit
Name Type Nullable Default Extra options Description
qcc_type binary No No default Length: 32 A key name, generally the base name of of the special page.
qcc_value integer No 0 Unsigned Some sort of stored value. Sizes, counts...
qcc_namespace integer No 0
qcc_title binary No "" Length: 255
qcc_namespacetwo integer No 0
qcc_titletwo binary No "" Length: 255

Indexes

edit
Name Columns Unique Description
qcc_type qcc_type, qcc_value No
qcc_title qcc_type, qcc_namespace, qcc_title No
qcc_titletwo qcc_type, qcc_namespacetwo, qcc_titletwo No

redirect

edit

For each redirect, this table contains exactly one row defining its target. Redirect targets are key to page_namespace/page_title of the target page. The target page may or may not exist, and due to renames and deletions may refer to different page records as time goes by.

Primary Key: rd_from

Columns

edit
Name Type Nullable Default Extra options Description
rd_from integer No 0 Unsigned Key to the page_id of the redirect page
rd_namespace integer No 0
rd_title binary No "" Length: 255
rd_interwiki string Yes NULL Length: 32
rd_fragment binary Yes NULL Length: 255

Indexes

edit
Name Columns Unique Description
rd_ns_title rd_namespace, rd_title, rd_from No

revision_actor_temp

edit

Temporary table to avoid blocking on an alter of revision. On large wikis like the English Wikipedia, altering the revision table is a months-long process. This table is being created to avoid such an alter, and will be merged back into revision in the future

Primary Key: revactor_rev, revactor_actor

Columns

edit
Name Type Nullable Default Extra options Description
revactor_rev integer No No default Unsigned Key to rev_id
revactor_actor bigint No No default Unsigned Key to actor_id
revactor_timestamp mwtimestamp No No default Copy field from revision for indexes
revactor_page integer No No default Unsigned Copy field from revision for indexes

Indexes

edit
Name Columns Unique Description
revactor_rev revactor_rev Yes
actor_timestamp revactor_actor, revactor_timestamp No
page_actor_timestamp revactor_page, revactor_actor, revactor_timestamp No

revision_comment_temp

edit

Temporary table to avoid blocking on an alter of revision. On large wikis like the English Wikipedia, altering the revision table is a months-long process. This table is being created to avoid such an alter, and will be merged back into revision in the future

Primary Key: revcomment_rev, revcomment_comment_id

Columns

edit
Name Type Nullable Default Extra options Description
revcomment_rev integer No No default Unsigned Key to rev_id
revcomment_comment_id bigint No No default Unsigned Key to comment_id

Indexes

edit
Name Columns Unique Description
revcomment_rev revcomment_rev Yes

site_identifiers

edit

Links local site identifiers to their corresponding site.

Primary Key: si_type, si_key

Columns

edit
Name Type Nullable Default Extra options Description
si_type binary No No default Length: 32 local key type, ie 'interwiki' or 'langlink'
si_key binary No No default Length: 32 local key value, ie 'en' or 'wiktionary'
si_site integer No No default Unsigned Key on site.site_id

Indexes

edit
Name Columns Unique Description
site_ids_site si_site No
site_ids_key si_key No

site_stats

edit

Contains a single row with some aggregate info on the state of the site.

Primary Key: ss_row_id

Columns

edit
Name Type Nullable Default Extra options Description
ss_row_id integer No No default Unsigned The single row should contain 1 here.
ss_total_edits bigint Yes NULL Unsigned Total number of edits performed.
ss_good_articles bigint Yes NULL Unsigned See SiteStatsInit::articles().
ss_total_pages bigint Yes NULL Unsigned Total pages, theoretically equal to SELECT COUNT(*) FROM page.
ss_users bigint Yes NULL Unsigned Number of users, theoretically equal to SELECT COUNT(*) FROM user.
ss_active_users bigint Yes NULL Unsigned Number of users that still edit.
ss_images bigint Yes NULL Unsigned Number of images, equivalent to SELECT COUNT(*) FROM image.

sites

edit

Holds all the sites known to the wiki.

Primary Key: site_id

Columns

edit
Name Type Nullable Default Extra options Description
site_id integer No No default Unsigned Numeric id of the site
site_global_key binary No No default Length: 64 Global identifier for the site, ie 'enwiktionary'
site_type binary No No default Length: 32 Type of the site, ie 'mediawiki'
site_group binary No No default Length: 32 Group of the site, ie 'wikipedia'
site_source binary No No default Length: 32 Source of the site data, ie 'local', 'wikidata', 'my-magical-repo'
site_language binary No No default Length: 35 Language code of the sites primary language.
site_protocol binary No No default Length: 32 Protocol of the site, ie 'http://', 'irc://', '//'. This field is an index for lookups and is build from type specific data in site_data.
site_domain string No No default Length: 255 Domain of the site in reverse order, ie 'org.mediawiki.www.'. This field is an index for lookups and is build from type specific data in site_data.
site_data blob No No default Length: 65530 Type dependent site data.
site_forward mwtinyint No No default Length: 1 If site.tld/path/key:pageTitle should forward users to the page on the actual site, where "key" is the local identifier.
site_config blob No No default Length: 65530 Type dependent site config. For instance if template transclusion should be allowed if it's a MediaWiki.

Indexes

edit
Name Columns Unique Description
site_global_key site_global_key Yes
site_type site_type No
site_group site_group No
site_source site_source No
site_language site_language No
site_protocol site_protocol No
site_domain site_domain No
site_forward site_forward No

slot_roles

edit

Normalization table for role names

Primary Key: role_id

Columns

edit
Name Type Nullable Default Extra options Description
role_id integer No No default
role_name binary No No default Length: 64

Indexes

edit
Name Columns Unique Description
role_name role_name Yes Index for looking up the internal ID of a role

slots

edit

Slots represent an n:m relation between revisions and content objects. A content object can have a specific "role" in one or more revisions. Each revision can have multiple content objects, each having a different role.

Primary Key: slot_revision_id, slot_role_id

Columns

edit
Name Type Nullable Default Extra options Description
slot_revision_id bigint No No default Unsigned reference to rev_id or ar_rev_id
slot_role_id smallint No No default Unsigned reference to role_id
slot_content_id bigint No No default Unsigned reference to content_id
slot_origin bigint No No default Unsigned The revision ID of the revision that originated the slot's content. To find revisions that changed slots, look for slot_origin = slot_revision_id. TODO: Is that actually true? Rollback seems to violate it by setting slot_origin to an older rev_id. Undeletions could result in the same situation.

Indexes

edit
Name Columns Unique Description
slot_revision_origin_role slot_revision_id, slot_origin, slot_role_id No Index for finding revisions that modified a specific slot
edit

Track template inclusions. The target page may or may not exist, and due to renames and deletions may refer to different page records as time goes by.

Primary Key: tl_from, tl_namespace, tl_title

Columns

edit
Name Type Nullable Default Extra options Description
tl_from integer No 0 Unsigned Key to the page_id of the page containing the link.
tl_from_namespace integer No 0 Namespace for this page
tl_namespace integer No 0
tl_title binary No "" Length: 255

Indexes

edit
Name Columns Unique Description
tl_namespace tl_namespace, tl_title, tl_from No Reverse index, for Special:Whatlinkshere
tl_backlinks_namespace tl_from_namespace, tl_namespace, tl_title, tl_from No Index for Special:Whatlinkshere with namespace filter

text

edit

Holds text of individual page revisions. Field names are a holdover from the 'old' revisions table in MediaWiki 1.4 and earlier: an upgrade will transform that table into the 'text' table to minimize unnecessary churning and downtime. If upgrading, the other fields will be left unused.

Primary Key: old_id

Columns

edit
Name Type Nullable Default Extra options Description
old_id integer No No default Unsigned Unique text storage key number. Note that the 'oldid' parameter used in URLs does *not* refer to this number anymore, but to rev_id. content.content_address refers to this column
old_text blob No No default Length: 16777215 Depending on the contents of the old_flags field, the text may be convenient plain text, or it may be funkily encoded.
old_flags blob No No default Length: 255 Comma-separated list of flags:
  • gzip: text is compressed with PHP's gzdeflate() function.
  • utf-8: text was stored as UTF-8. If $wgLegacyEncoding option is on, rows *without* this flag will be converted to UTF-8 transparently at load time. Note that due to a bug in a maintenance script, this flag may have been stored as 'utf8' in some cases (T18841).
  • object: text field contained a serialized PHP object. The object either contains multiple versions compressed together to achieve a better compression ratio, or it refers to another row where the text can be found.
  • external: text was stored in an external location specified by old_text. Any additional flags apply to the data stored at that URL, not the URL itself. The 'object' flag is *not* set for URLs of the form 'DB://cluster/id/itemid', because the external storage system itself decompresses these.

updatelog

edit

A table to log updates, one text key row per update.

Primary Key: ul_key

Columns

edit
Name Type Nullable Default Extra options Description
ul_key string No No default Length: 255
ul_value blob Yes No default Length: 65530

uploadstash

edit

Store information about newly uploaded files before they're moved into the actual filestore

Primary Key: us_id

Columns

edit
Name Type Nullable Default Extra options Description
us_id integer No No default Unsigned
us_user integer No No default Unsigned the user who uploaded the file.
us_key string No No default Length: 255 file key. this is how applications actually search for the file. this might go away, or become the primary key.
us_orig_path string No No default Length: 255 the original path
us_path string No No default Length: 255 the temporary path at which the file is actually stored
us_source_type string Yes No default Length: 50 which type of upload the file came from (sometimes)
us_timestamp mwtimestamp No No default Length: 14 the date/time on which the file was added
us_status string No No default Length: 50
us_chunk_inx integer Yes No default Unsigned chunk counter starts at 0, current offset is stored in us_size
us_props blob Yes No default Length: 65530 Serialized file properties from FSFile::getProps()
us_size integer No No default Unsigned file size in bytes
us_sha1 string No No default Length: 31 this hash comes from FSFile::getSha1Base36(), and is 31 characters
us_mime string Yes No default Length: 255
us_media_type mwenum Yes NULL Media type as defined by the MEDIATYPE_xxx constants, should duplicate definition in the image table
us_image_width integer Yes No default Unsigned image-specific properties
us_image_height integer Yes No default Unsigned
us_image_bits smallint Yes No default Unsigned

Indexes

edit
Name Columns Unique Description
us_user us_user No sometimes there's a delete for all of a user's stuff.
us_key us_key Yes pick out files by key, enforce key uniqueness
us_timestamp us_timestamp No the abandoned upload cleanup script needs this

user_former_groups

edit

Stores the groups the user has once belonged to. The user may still belong to these groups (check user_groups). Autopromotion of users to groups from which they were removed can be restricted by using wgAutopromoteOnce instead of wgAutopromote.

Primary Key: ufg_user, ufg_group

Columns

edit
Name Type Nullable Default Extra options Description
ufg_user integer No 0 Unsigned Key to user_id
ufg_group binary No "" Length: 255

user_groups

edit

User permissions have been broken out to a separate table; this allows sites with a shared user table to have different permissions assigned to a user in each project. This table replaces the old user_rights field which used a comma-separated blob.

Primary Key: ug_user, ug_group

Columns

edit
Name Type Nullable Default Extra options Description
ug_user integer No 0 Unsigned Key to user_id
ug_group binary No "" Length: 255 Group names are short symbolic string keys. The set of group names is open-ended, though in practice only some predefined ones are likely to be used. At runtime $wgGroupPermissions will associate group keys with particular permissions. A user will have the combined permissions of any group they're explicitly in, plus the implicit '*' and 'user' groups.
ug_expiry mwtimestamp Yes NULL Time at which the user group membership will expire. Set to NULL for a non-expiring (infinite) membership.

Indexes

edit
Name Columns Unique Description
ug_group ug_group No
ug_expiry ug_expiry No

user_newtalk

edit

Stores notifications of user talk page changes, for the display of the 'you have new messages' box

Columns

edit
Name Type Nullable Default Extra options Description
user_id integer No 0 Unsigned
user_ip binary No "" Length: 40 If the user is an anonymous user their IP address is stored here since the user_id of 0 is ambiguous
user_last_timestamp mwtimestamp Yes No default The highest timestamp of revisions of the talk page viewed by this user

Indexes

edit
Name Columns Unique Description
un_user_id user_id No
un_user_ip user_ip No

user_properties

edit

User preferences and perhaps other fun stuff. :) Replaces the old user.user_options blob, with a couple nice properties: 1) We only store non-default settings, so changes to the defaults are now reflected for everybody, not just new accounts. 2) We can more easily do bulk lookups, statistics, or modifications of saved options since it's a sane table structure.

Primary Key: up_user, up_property

Columns

edit
Name Type Nullable Default Extra options Description
up_user integer No No default Unsigned Foreign key to user.user_id
up_property binary No No default Length: 255 Name of the option being saved. This is indexed for bulk lookup.
up_value blob Yes No default Length: 65530 Property value as a string.

Indexes

edit
Name Columns Unique Description
up_property up_property No

watchlist

edit

Primary Key: wl_id

Columns

edit
Name Type Nullable Default Extra options Description
wl_id integer No No default Unsigned
wl_user integer No No default Unsigned
wl_namespace integer No 0
wl_title binary No "" Length: 255
wl_notificationtimestamp mwtimestamp Yes No default Timestamp used to send notification e-mails and show 'updated since last visit' markers. Set to NULL when the user visits the latest revision of the page, which means that they should be sent an e-mail on the next change.

Indexes

edit
Name Columns Unique Description
wl_user wl_user, wl_namespace, wl_title Yes Special:Watchlist
wl_namespace_title wl_namespace, wl_title No Special:Movepage (WatchedItemStore::duplicateEntry)
wl_user_notificationtimestamp wl_user, wl_notificationtimestamp No ApiQueryWatchlistRaw changed filter

watchlist_expiry

edit

Allows setting an expiry for watchlist items.

Primary Key: we_item

Columns

edit
Name Type Nullable Default Extra options Description
we_item integer No No default Unsigned Key to watchlist.wl_id
we_expiry mwtimestamp No No default Expiry time

Indexes

edit
Name Columns Unique Description
we_expiry we_expiry No