Manual:Database layout/diagram/1.34.0

Full screen

Database schema of MediaWiki 1.34.0 (December 2019).
Refer to https://www.mediawiki.org/wiki/DB for more details.

User

user

  • user_id INT
  • user_name VARCHAR(255)
  • user_real_name VARCHAR(255)
  • user_password TINYBLOB
  • user_newpassword TINYBLOB
  • user_newpass_time BINARY(14)
  • user_email TINYTEXT
  • user_touched BINARY(14)
  • user_token BINARY(32)
  • user_email_authenticated BINARY(14)
  • user_email_token BINARY(32)
  • user_email_token_expires BINARY(14)
  • user_registration BINARY(14)
  • user_editcount INT
  • user_password_expires VARBINARY(14)

user_properties

  • up_user INT
  • up_property VARBINARY(255)
  • up_value BLOB

user_newtalk

  • user_id INT
  • user_ip VARBINARY(40)
  • user_last_timestamp VARBINARY(14)

actor

  • actor_id BIGINT
  • actor_user INT
  • actor_name VARCHAR(255)

bot_passwords

  • bp_user INT
  • bp_app_id VARBINARY(32)
  • bp_password TINYBLOB
  • bp_token BINARY(32)
  • bp_restrictions BLOB
  • bp_grants BLOB

Permissions

user_groups

  • ug_user INT
  • ug_group VARBINARY(255)
  • ug_expiry VARBINARY(14)

user_former_groups

  • ufg_user INT
  • ufg_group VARBINARY(255)

page_restrictions

  • pr_id INT
  • pr_page INT
  • pr_type VARBINARY(60)
  • pr_level VARBINARY(60)
  • pr_cascade TINYINT
  • pr_user INT
  • pr_expiry VARBINARY(14)

protected_titles

  • pt_namespace INT
  • pt_title VARCHAR(255)
  • pt_user INT
  • pt_reason_id BIGINT
  • pt_timestamp BINARY(14)
  • pt_expiry VARBINARY(14)
  • pt_create_perm VARBINARY(60)

ipblocks

  • ipb_id INT
  • ipb_address TINYBLOB
  • ipb_user INT
  • ipb_by_actor BIGINT
  • ipb_reason_id BIGINT
  • ipb_timestamp BINARY(14)
  • ipb_auto BOOL
  • ipb_anon_only BOOL
  • ipb_create_account BOOL
  • ipb_enable_autoblock BOOL
  • ipb_expiry VARBINARY(14)
  • ipb_range_start TINYBLOB
  • ipb_range_end TINYBLOB
  • ipb_deleted BOOL
  • ipb_block_email BOOL
  • ipb_allow_usertalk BOOL
  • ipb_parent_block_id INT
  • ipb_sitewide BOOL

ipblocks_restrictions

  • ir_ipb_id INT
  • ir_type TINYINT(1)
  • ir_value INT

Logging

logging

  • log_id INT
  • log_type VARBINARY(32)
  • log_action VARBINARY(32)
  • log_timestamp BINARY(14)
  • log_actor BIGINT
  • log_namespace INT
  • log_title VARCHAR(255)
  • log_page INT
  • log_comment_id BIGINT
  • log_params BLOB
  • log_deleted TINYINT

log_search

  • ls_field VARBINARY(32)
  • ls_value VARCHAR(255)
  • ls_log_id INT

comment

  • comment_id BIGINT
  • comment_hash INT
  • comment_text BLOB
  • comment_data BLOB

Tags

change_tag

  • ct_id INT
  • ct_rc_id INT
  • ct_log_id INT
  • ct_rev_id INT
  • ct_params BLOB
  • ct_tag_id INT

change_tag_def

  • ctd_id INT
  • ctd_name VARBINARY(255)
  • ctd_user_defined TINYINT(1)
  • ctd_count BIGINT

Recent changes

recentchanges

  • rc_id INT
  • rc_timestamp VARBINARY(14)
  • rc_actor BIGINT
  • rc_namespace INT
  • rc_title VARCHAR(255)
  • rc_comment_id BIGINT
  • rc_minor TINYINT
  • rc_bot TINYINT
  • rc_new TINYINT
  • rc_cur_id INT
  • rc_this_oldid INT
  • rc_last_oldid INT
  • rc_type TINYINT
  • rc_source VARCHAR(16)
  • rc_patrolled TINYINT
  • rc_ip VARBINARY(40)
  • rc_old_len INT
  • rc_new_len INT
  • rc_deleted TINYINT
  • rc_logid INT
  • rc_log_type VARBINARY(255)
  • rc_log_action VARBINARY(255)
  • rc_params BLOB

watchlist

  • wl_id INT
  • wl_user INT
  • wl_namespace INT
  • wl_title VARCHAR(255)
  • wl_notificationtimestamp VARBINARY(14)

Pages

page

  • page_id INT
  • page_namespace INT
  • page_title VARCHAR(255)
  • page_restrictions TINYBLOB
  • page_is_redirect TINYINT
  • page_is_new TINYINT
  • page_random REAL
  • page_touched BINARY(14)
  • page_links_updated VARBINARY(14)
  • page_latest INT
  • page_len INT
  • page_content_model VARBINARY(32)
  • page_lang VARBINARY(35)

page_props

  • pp_page INT
  • pp_propname VARBINARY(60)
  • pp_value BLOB
  • pp_sortkey FLOAT

archive

  • ar_id INT
  • ar_namespace INT
  • ar_title VARCHAR(255)
  • ar_comment_id BIGINT
  • ar_actor BIGINT
  • ar_timestamp BINARY(14)
  • ar_minor_edit TINYINT
  • ar_rev_id INT
  • ar_text_id INT
  • ar_deleted TINYINT
  • ar_len INT
  • ar_page_id INT
  • ar_parent_id INT
  • ar_sha1 VARBINARY(32)
  • ar_content_model VARBINARY(32)
  • ar_content_format VARBINARY(64)

redirect

  • rd_from INT
  • rd_namespace INT
  • rd_title VARCHAR(255)
  • rd_interwiki VARCHAR(32)
  • rd_fragment VARCHAR(255)

category

  • cat_id INT
  • cat_title VARCHAR(255)
  • cat_pages INT
  • cat_subcats INT
  • cat_files INT

Revisions

revision

  • rev_id INT
  • rev_page INT
  • rev_text_id INT
  • rev_comment VARBINARY(767)
  • rev_user INT
  • rev_user_text VARCHAR(255)
  • rev_timestamp BINARY(14)
  • rev_minor_edit TINYINT
  • rev_deleted TINYINT
  • rev_len INT
  • rev_parent_id INT
  • rev_sha1 VARBINARY(32)
  • rev_content_model VARBINARY(32)
  • rev_content_format VARBINARY(64)

slots

  • slot_revision_id BIGINT
  • slot_role_id SMALLINT
  • slot_content_id BIGINT
  • slot_origin BIGINT

slot_roles

  • role_id SMALLINT
  • role_name VARBINARY(64)

ip_changes

  • ipc_rev_id INT
  • ipc_rev_timestamp BINARY(14)
  • ipc_hex VARBINARY(35)

content

  • content_id BIGINT
  • content_size INT
  • content_sha1 VARBINARY(32)
  • content_model SMALLINT
  • content_address VARBINARY(255)

content_models

  • model_id SMALLINT
  • model_name VARBINARY(64)

text

  • old_id INT
  • old_text MEDIUMBLOB
  • old_flags TINYBLOB

Link tables

pagelinks

  • pl_from INT
  • pl_from_namespace INT
  • pl_namespace INT
  • pl_title VARCHAR(255)

iwlinks

  • iwl_from INT
  • iwl_prefix VARBINARY(20)
  • iwl_title VARCHAR(255)

externallinks

  • el_id INT
  • el_from INT
  • el_to BLOB
  • el_index BLOB
  • el_index_60 VARBINARY(60)

langlinks

  • ll_from INT
  • ll_lang VARBINARY(20)
  • ll_title VARCHAR(255)

imagelinks

  • il_from INT
  • il_from_namespace INT
  • il_to VARCHAR(255)

templatelinks

  • tl_from INT
  • tl_from_namespace INT
  • tl_namespace INT
  • tl_title VARCHAR(255)

categorylinks

  • cl_from INT
  • cl_to VARCHAR(255)
  • cl_sortkey VARBINARY(230)
  • cl_sortkey_prefix VARCHAR(255)
  • cl_timestamp TIMESTAMP
  • cl_collation VARBINARY(32)
  • cl_type ENUM(…)

Statistics

site_stats

  • ss_row_id INT
  • ss_total_edits BIGINT
  • ss_good_articles BIGINT
  • ss_total_pages BIGINT
  • ss_users BIGINT
  • ss_active_users BIGINT
  • ss_images BIGINT

Search

searchindex

  • si_page INT
  • si_title VARCHAR(255)
  • si_text MEDIUMTEXT

Maintenance

job

  • job_id INT
  • job_cmd VARBINARY(60)
  • job_namespace INT
  • job_title VARCHAR(255)
  • job_timestamp VARBINARY(14)
  • job_params MEDIUMBLOB
  • job_random INTEGER
  • job_attempts INTEGER
  • job_token VARBINARY(32)
  • job_token_timestamp VARBINARY(14)
  • job_sha1 VARBINARY(32)

updatelog

  • ul_key VARCHAR(255)
  • ul_value BLOB

Multimedia

image

  • img_name VARCHAR(255)
  • img_size INT
  • img_width INT
  • img_height INT
  • img_metadata MEDIUMBLOB
  • img_bits INT
  • img_media_type ENUM(…)
  • img_major_mime ENUM(…)
  • img_minor_mime VARBINARY(100)
  • img_description_id BIGINT
  • img_actor BIGINT
  • img_timestamp VARBINARY(14)
  • img_sha1 VARBINARY(32)

oldimage

  • oi_name VARCHAR(255)
  • oi_archive_name VARCHAR(255)
  • oi_size INT
  • oi_width INT
  • oi_height INT
  • oi_bits INT
  • oi_description_id BIGINT
  • oi_actor BIGINT
  • oi_timestamp BINARY(14)
  • oi_metadata MEDIUMBLOB
  • oi_media_type ENUM(…)
  • oi_major_mime ENUM(…)
  • oi_minor_mime VARBINARY(100)
  • oi_deleted TINYINT
  • oi_sha1 VARBINARY(32)

filearchive

  • fa_id INT
  • fa_name VARCHAR(255)
  • fa_archive_name VARCHAR(255)
  • fa_storage_group VARBINARY(16)
  • fa_storage_key VARBINARY(64)
  • fa_deleted_user INT
  • fa_deleted_timestamp BINARY(14)
  • fa_deleted_reason_id BIGINT
  • fa_size INT
  • fa_width INT
  • fa_height INT
  • fa_metadata MEDIUMBLOB
  • fa_bits INT
  • fa_media_type ENUM(…)
  • fa_major_mime ENUM(…)
  • fa_minor_mime VARBINARY(100)
  • fa_description_id BIGINT
  • fa_actor BIGINT
  • fa_timestamp BINARY(14)
  • fa_deleted TINYINT
  • fa_sha1 VARBINARY(32)

uploadstash

  • us_id INT
  • us_user INT
  • us_key VARCHAR(255)
  • us_orig_path VARCHAR(255)
  • us_path VARCHAR(255)
  • us_source_type VARCHAR(50)
  • us_timestamp VARBINARY(14)
  • us_status VARCHAR(50)
  • us_chunk_inx INT
  • us_props BLOB
  • us_size INT
  • us_sha1 VARCHAR(31)
  • us_mime VARCHAR(255)
  • us_media_type ENUM(…)
  • us_image_width INT
  • us_image_height INT
  • us_image_bits SMALLINT

Interwiki

sites

  • site_id INT
  • site_global_key VARBINARY(32)
  • site_type VARBINARY(32)
  • site_group VARBINARY(32)
  • site_source VARBINARY(32)
  • site_language VARBINARY(32)
  • site_protocol VARBINARY(32)
  • site_domain VARCHAR(255)
  • site_data BLOB
  • site_forward BOOL
  • site_config BLOB

site_identifiers

  • si_site INT
  • si_type VARBINARY(32)
  • si_key VARBINARY(32)

interwiki

  • iw_prefix VARCHAR(32)
  • iw_url BLOB
  • iw_api BLOB
  • iw_wikiid VARCHAR(64)
  • iw_local BOOL
  • iw_trans TINYINT

Caching tables

querycache

  • qc_type VARBINARY(32)
  • qc_value INT
  • qc_namespace INT
  • qc_title VARCHAR(255)

objectcache

  • keyname VARBINARY(255)
  • value MEDIUMBLOB
  • exptime DATETIME

querycachetwo

  • qcc_type VARBINARY(32)
  • qcc_value INT
  • qcc_namespace INT
  • qcc_title VARCHAR(255)
  • qcc_namespacetwo INT
  • qcc_titletwo VARCHAR(255)

querycache_info

  • qci_type VARBINARY(32)
  • qci_timestamp BINARY(14)

l10n_cache

  • lc_lang VARBINARY(32)
  • lc_key VARCHAR(255)
  • lc_value MEDIUMBLOB

ResourceLoader

module_deps

  • md_module VARBINARY(255)
  • md_skin VARBINARY(32)
  • md_deps MEDIUMBLOB

Credit: Nick Jenkins, Timo Tijhof / CC BY-SA 3.0

From https://www.mediawiki.org/wiki/Manual:Database_layout/diagram/1.34.0