Manual:Page table

The page table can be considered the "core of the wiki". Each page in a MediaWiki installation has an entry here which identifies it by title and contains some essential metadata. It was first introduced in r6710, in MediaWiki 1.5.

The text of the page itself is stored in the text table. To retrieve the text of an article, MediaWiki first searches for page_title in the page table. Then, page_latest is used to search the revision table for rev_id, and rev_text_id is obtained in the process. The value obtained for rev_text_id is used to search for old_id in the text table to retrieve the text. When a page is deleted, the revisions are moved to the archive table.

If you want to completely delete a page manually from the database, be sure to delete the entry for the page in the page table, and for all the page's revisions in the revision table, and all of the text rows corresponding only to the page in the text table. This can be done by deleting the page row, then running maintenance/deleteOrphanedRevisions.php. See also title 'Deleting pages with their relationships in text and revision tables' below how to do.

page_id
Uniquely identifying primary key. This value is preserved across edits and renames. There is an analogous field in the archive table to preserve this value in MediaWiki 1.11 and later; however, it is not used in Special:Undelete, the interface for undeleting pages used by project administrators. For example, for this page, page_id = 10501. 

page_namespace
A page name is broken into a namespace and a title. The namespace keys are UI-language-independent constants, defined in includes/Defines.php.

This field contains the number of the page's namespace. The values range from 0 to 15 for the standard namespaces, and from 100 to 2147483647 for custom namespaces.

page_title
The sanitized page title, without the title of its namespace with a maximum of 255 characters (binary), e.g. "[255 chars]" or "Talk:[255 chars]" or "Category discussion:[255 chars here]". It is stored as text, with spaces replaced by underscores. The real title shown in articles is just this title with underscores (_) converted to spaces.

page_restrictions
Comma-separated set of permission keys indicating who can move or edit the page. Edit and move sections are separated by a colon (e.g., "edit=autoconfirmed,sysop:move=sysop").

Beginning with MediaWiki 1.10, page protection controls were moved to the page restrictions table, so this field will be empty in databases generated by more current versions of MediaWiki.

page_counter
Number of times this page has been viewed. Note that on some sites (e.g. Wikimedia sites) incrementing this field is disabled so as to increase performance - see the $wgDisableCounters global.

page_is_redirect
A value of 1 here indicates the article is a redirect; it is 0 in all other cases.

page_is_new
This field stores whether the page is a new entry or not; if the field contains a value of 1, then it indicates that the page is a new entry with only one edit. It is 0 in all other cases.

page_random
Random decimal value, between 0 and 1, used for Special:Random (see Manual:Random page for more details). Generated by wfRandom.

page_touched
This timestamp is updated whenever the page changes in a way requiring it to be re-rendered, invalidating caches. Aside from editing this includes permission changes, creation or deletion of linked pages, and alteration of contained templates. Set to $dbw->timestamp at the time of page creation.

page_links_updated
This timestamp is updated whenever a page is re-parsed and it has all the link tracking tables updated for it. This is useful for de-duplicating expensive backlink update jobs. Set to the default value of NULL when the page is created by WikiPage::insertOn.

page_latest
This is a foreign key to rev_id for the current revision. It may be 0 during page creation. It needs to link to a revision with a valid, or there will be the "The revision #0 of the page named 'Foo' does not exist" error when one tries to view the page.

page_len
Uncompressed length in bytes of the page's current source text.

page_content_model
Content model, see CONTENT_MODEL_XXX constants. Comparable to.

page_lang
Page content language. Set to the default value of NULL at the time of page creation.

Schema summary
MariaDB> describe page; ++-+--+-+++ ++-+--+-+++ ++-+--+-+++ 13 rows in set (0.00 sec)
 * Field             | Type                | Null | Key | Default        | Extra          |
 * page_id           | int(10) unsigned    | NO   | PRI | NULL           | auto_increment |
 * page_namespace    | int(11)             | NO   | MUL | NULL           |                |
 * page_title        | varbinary(255)      | NO   |     | NULL           |                |
 * page_restrictions | tinyblob            | NO   |     | NULL           |                |
 * page_counter      | bigint(20) unsigned | NO   |     | 0              |                |
 * page_is_redirect  | tinyint(3) unsigned | NO   | MUL | 0              |                |
 * page_is_new       | tinyint(3) unsigned | NO   |     | 0              |                |
 * page_random       | double unsigned     | NO   | MUL | NULL           |                |
 * page_touched      | binary(14)          | NO   |     |                |                |
 * page_latest       | int(10) unsigned    | NO   |     | NULL           |                |
 * page_len          | int(10) unsigned    | NO   | MUL | NULL           |                |
 * page_content_model | varbinary(32)      | YES  |     | NULL           |                |
 * page_links_updated | varbinary(14)      | YES  |     | NULL           |                |

mysql> describe page; ++-+--+-+++ ++-+--+-+++ ++-+--+-+++ 12 rows in set (0.00 sec)
 * Field             | Type                | Null | Key | Default        | Extra          |
 * page_id           | int(10) unsigned    | NO   | PRI | NULL           | auto_increment |
 * page_namespace    | int(11)             | NO   | MUL | NULL           |                |
 * page_title        | varbinary(255)      | NO   |     | NULL           |                |
 * page_restrictions | tinyblob            | NO   |     | NULL           |                |
 * page_counter      | bigint(20) unsigned | NO   |     | 0              |                |
 * page_is_redirect  | tinyint(3) unsigned | NO   | MUL | 0              |                |
 * page_is_new       | tinyint(3) unsigned | NO   |     | 0              |                |
 * page_random       | double unsigned     | NO   | MUL | NULL           |                |
 * page_touched      | binary(14)          | NO   |     |                |                |
 * page_latest       | int(10) unsigned    | NO   |     | NULL           |                |
 * page_len          | int(10) unsigned    | NO   | MUL | NULL           |                |
 * page_content_model | varbinary(32)      | YES  |     | NULL           |                |

DESCRIBE page; in MediaWiki 1.19 results in: +---+-+--+-+++ +---+-+--+-+++ +---+-+--+-+++
 * Field            | Type                | Null | Key | Default        | Extra          |
 * page_id          | int(10) unsigned    | NO   | PRI | NULL           | auto_increment |
 * page_namespace   | int(11)             | NO   | MUL | NULL           |                |
 * page_title       | varbinary(255)      | NO   |     | NULL           |                |
 * page_restrictions | tinyblob           | NO   |     | NULL           |                |
 * page_counter     | bigint(20) unsigned | NO   |     | 0              |                |
 * page_is_redirect | tinyint(3) unsigned | NO   | MUL | 0              |                |
 * page_is_new      | tinyint(3) unsigned | NO   |     | 0              |                |
 * page_random      | double unsigned     | NO   | MUL | NULL           |                |
 * page_touched     | binary(14)          | NO   |     |                |                |
 * page_latest      | int(10) unsigned    | NO   |     | NULL           |                |
 * page_len         | int(10) unsigned    | NO   | MUL | NULL           |                |

DESCRIBE page; in MediaWiki 1.12 results in: +---+-+--+-+-++ +---+-+--+-+-++ +---+-+--+-+-++
 * Field                | Type                | Null | Key | Default | Extra          |
 * page_id              | int(8) unsigned     | NO   | PRI | NULL    | auto_increment |
 * page_namespace       | int(11)             | NO   | MUL | NULL    |                |
 * page_title           | varchar(255)        | NO   |     | NULL    |                |
 * page_restrictions    | tinyblob            | NO   |     | NULL    |                |
 * page_counter         | bigint(20) unsigned | NO   |     | 0       |                |
 * page_is_redirect     | tinyint(1) unsigned | NO   |     | 0       |                |
 * page_is_new          | tinyint(1) unsigned | NO   |     | 0       |                |
 * page_random          | double unsigned     | NO   | MUL | NULL    |                |
 * page_touched         | char(14)            | NO   |     | NULL    |                |
 * page_latest          | int(8) unsigned     | NO   |     | NULL    |                |
 * page_len             | int(8) unsigned     | NO   | MUL | NULL    |                |

DESCRIBE page; in MediaWiki 1.11 results in: +---+-+--+-+-++ +---+-+--+-+-++ +---+-+--+-+-++
 * Field                | Type                | Null | Key | Default | Extra          |
 * page_id              | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
 * page_namespace       | int(11)             | NO   | MUL | NULL    |                |
 * page_title           | varchar(255)        | NO   |     | NULL    |                |
 * page_restrictions    | tinyblob            | NO   |     | NULL    |                |
 * page_counter         | bigint(20) unsigned | NO   |     | 0       |                |
 * page_is_redirect     | tinyint(3) unsigned | NO   |     | 0       |                |
 * page_is_new          | tinyint(3) unsigned | NO   |     | 0       |                |
 * page_random          | double unsigned     | NO   | MUL | NULL    |                |
 * page_touched         | binary(14)          | NO   |     | NULL    |                |
 * page_latest          | int(10) unsigned    | NO   |     | NULL    |                |
 * page_len             | int(10) unsigned    | NO   | MUL | NULL    |                |

DESCRIBE page;</tt> in MediaWiki 1.5 through 1.10 results in: +---+-+--+-+-++ +---+-+--+-+-++ +---+-+--+-+-++
 * Field                | Type                | Null | Key | Default | Extra          |
 * page_id              | int(8) unsigned     | NO   | PRI | NULL    | auto_increment |
 * page_namespace       | int(11)             | NO   | MUL | NULL    |                |
 * page_title           | varchar(255)        | NO   |     | NULL    |                |
 * page_restrictions    | tinyblob            | NO   |     | NULL    |                |
 * page_counter         | bigint(20) unsigned | NO   |     | 0       |                |
 * page_is_redirect     | tinyint(1) unsigned | NO   |     | 0       |                |
 * page_is_new          | tinyint(1) unsigned | NO   |     | 0       |                |
 * page_random          | double unsigned     | NO   | MUL | NULL    |                |
 * page_touched         | char(14)            | NO   |     | NULL    |                |
 * page_latest          | int(8) unsigned     | NO   |     | NULL    |                |
 * page_len             | int(8) unsigned     | NO   | MUL | NULL    |                |

Indices
mysql> show index in page; +---++-+--+--+---+-+--++--++-+---+ +---++-+--+--+---+-+--++--++-+---+ +---++-+--+--+---+-+--++--++-+---+
 * Table | Non_unique | Key_name                   | Seq_in_index | Column_name      | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
 * page |          0 | PRIMARY                     |            1 | page_id          | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
 * page |          0 | name_title                  |            1 | page_namespace   | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
 * page |          0 | name_title                  |            2 | page_title       | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
 * page |          1 | page_random                 |            1 | page_random      | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
 * page |          1 | page_len                    |            1 | page_len         | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
 * page |          1 | page_redirect_namespace_len |            1 | page_is_redirect | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
 * page |          1 | page_redirect_namespace_len |            2 | page_namespace   | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
 * page |          1 | page_redirect_namespace_len |            3 | page_len         | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |

Listing pages and relations with other essential tables
The following code will select the most recent versions of all articles from core tables: [page, revision, text]:

Other important considerations:
 * to find undeleted pages add "r.rev_deleted = 0"
 * to find pages in namespace 0 add "p.page_namespace = 0"
 * to find pages that are not redirects add "p.page_is_redirect = 0"

These additional statements can be added either as conditions to a Where statement or as conditions on the appropriate Inner Join statement.

Deleting pages with their relationships in text and revision tables
The following shows a steps to run to help you delete pages and their children from the main pages in the page, revision and text tables.

Listing all related table keys

Deleting from database the rows

You should have a result set like this

Replace the concatenated id in the following query

After, you can do some cleanup using this maintenance script: