User:RichMorin/MySQL Tables

The following information reflects my own explorations into MediaWiki's database usage. Each list item header contains two parts. The left-hand part (e.g., "archive table") is a link to the "official" information, which resides under Help:Database_layout. The right-hand part (e.g., "mw_archive") is a link to my exploratory version.

MediaWiki uses about two dozen MySQL tables. These can be examined interactvely, using the mysql(1) command. They can also be dumped into XML and/or loaded into the wiki. See Data dumps for more information.

• archive table - mw_archive

  Holding area for deleted articles, which may be viewed or restored by admins through the Special:Undelete interface. The fields generally correspond to the page, revision, and text fields, with several caveats.

• categorylinks table - mw_categorylinks

  Track category inclusions *used inline*. This tracks a single level of category membership (folksonomic tagging, really).

• externallinks table - mw_externallinks

  Track links to external URLs.

• hitcounter table - mw_hitcounter

  Stores an ID for every time any article is visited; depending on $wgHitcounterUpdateFreq, it is periodically cleared and the page_counter column in the page table is updated for all of the articles that have been visited.

• image table - mw_image

  Uploaded images and other files.

• imagelinks table - mw_imagelinks

  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.

• interwiki table - mw_interwiki

  Recognized interwiki link prefixes.

• ipblocks table - mw_ipblocks

  The Internet is full of jerks, alas. Sometimes it's handy to block a vandal or troll account.

• jobs table - mw_jobs

  Jobs performed by parallel Apache threads or a command-line daemon.

• logging table - mw_logging

  Activity logging information.

• math table - mw_math

  Used by texvc math-rendering extension to keep track of previously-rendered items.

• objectcache table - mw_objectcache

  For a few generic cache operations if not using Memcached.

• oldimage table - mw_oldimage

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

• page table - mw_page

  Core of the wiki: each page has an entry here which identifies it by title and contains some essential metadata.

• pagelinks table - mw_pagelinks

  Track page-to-page hyperlinks within the wiki.

• querycache table - mw_querycache

  Used for caching expensive grouped queries.

• recentchanges table - mw_recentchanges

  Primarily a summary table for Special:Recentchanges, this table contains some additional info on edits from the last few days.

• revision table - mw_revision

  Every edit of a page creates also a revision row. This stores metadata about the revision, and a reference to the text storage backend.

• searchindex table - mw_searchindex

  When using the default MySQL search backend, page titles and text are munged to strip markup, do Unicode case folding, and prepare the result for MySQL's fulltext index.

  This table must be MyISAM; InnoDB does not support the needed fulltext index.

• site_stats table - mw_site_stats

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

• templatelinks table - mw_templatelinks

  Track template inclusions.

• text table - mw_text

  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.

• trackbacks table - mw_trackbacks (unused at this time)

  Contains page trackback information.

• transcache table - mw_transcache

  Cache of InterWiki transclusions.

• user table - mw_user

  Contains information about users.

• user_groups table - mw_user_groups

  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.

• user_newtalk table - mw_user_newtalk

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

• validate table - mw_validate

  For article validation (unused at this time; the validation code has been removed).

• watchlist table - mw_watchlist

  Contains watchlist information.

Semantic MediaWiki currently adds three database tables:

• attributes table - smw_attributes

  Contains attributes for RDF subjects (e.g., pages).

• relations table - smw_relations

  Contains relations between RDF subjects (e.g., pages) and objects (e.g., pages, URIs).

• special properties table - smw_specprops

  Contains special properties for RDF subjects (e.g., pages).

• Repairing MySQL Tables on Linux Server