Manual:Revision table

The revision table holds metadata for every edit done to a page within the wiki. Every edit of a page creates a revision row, which holds information such as the user who made the edit, the time at which the edit was made, and a reference to the new wikitext in the text table.

Note that a row is partly about the edit operation and partly about the result of that operation, the new wikitext. It does not give a reference to the old wikitext.

Import of the last revision of a page from another wiki produces two entries in the revision table, one with the date and wikitext of the imported revision, and one with the import date. The wikitext of the latter, which becomes that of the current page, is, if a page with the same name already existed, that of the more recent of the two pages.

The revision table is very similar to the recentchanges table. The revision table is used for page history and user contributions listings. The recentchanges table is used for recent changes, related changes, watchlists, and, in the case of page creation, for the list of new pages.

Differences include:
 * The recentchanges table also records logged events such as page moves and deletions
 * Items in the recentchanges table are periodically purged; those in the revision table are typically kept longer or indefinitely.
 * as said, import of a page revision not only adds an entry to the revision table with the import date, but also one with the original date.

Deleted revisions are moved to the archive table.

rev_id
This field holds the primary key for each revision. page_latest is a foreign key to this field.

rev_page
This field holds a reference to the page to which this revision pertains. The number in this field is equal to the page_id field of said page. This should never be invalid.

rev_text_id
This is a foreign key to old_id in the text table. (The text table is where the actual bulk text is stored.) It's possible for multiple revisions to use the same text&mdash;for instance, revisions where only metadata is altered, or where a rollback is done to a previous version.

rev_comment
This field holds an editor's edit summary (editor's comment on revision). This text is shown in the history and contributions. (The recentchanges table contains a copy used for recent changes, related changes, watchlists, and, in the case of page creation, for the list of new pages.) It is rendered in a sanitized subset of wiki markup.

rev_user
This is equal to the user_id of the user who made this edit. The value for this field is 0 for anonymous edits, initializations scripts, and for some mass imports.

rev_user_text
This field holds the text of the editor's username, or the IP address of the editor if the revision was done by an unregistered user.

In anonymous revisions imported from UseModWiki or early incarnations of the Phase II software, this field may contain an IP address with the final octet obscured (i.e.  such as 24.150.61.xxx; see bug 3631). Some edits imported from UseModWiki may contain a Reverse DNS lookup hostname like  or.

rev_timestamp
Holds the timestamp of the edit.

rev_minor_edit
Records whether the user marked the 'minor edit' checkbox. If the value for this field is 1, then the edit was declared as 'minor'; it is 0 otherwise. Many automated edits are marked as minor.

rev_deleted
This field is reserved for the RevisionDelete system.

rev_len
This field contains the length of the article after the revision, in bytes. Used in history pages. Corresponds to rc_new_len.

rev_parent_id
The rev_id of the previous revision to the page. This field is used to add support for a tree structure (The Adjacency List Model). Corresponds to rc_last_oldid.

rev_sha1
This field is used to add the SHA-1 text content hash in base-36.

rev_content_model
Content model, see CONTENT_MODEL_XXX constants

rev_content_format
content format, see CONTENT_FORMAT_XXX constants

Schema summary
mysql> describe revision; ++-+--+-+++ ++-+--+-+++ ++-+--+-+++ 14 rows in set (0.00 sec)
 * Field             | Type                | Null | Key | Default        | Extra          |
 * rev_id            | int(10) unsigned    | NO   | PRI | NULL           | auto_increment |
 * rev_page          | int(10) unsigned    | NO   | MUL | NULL           |                |
 * rev_text_id       | int(10) unsigned    | NO   |     | NULL           |                |
 * rev_comment       | tinyblob            | NO   |     | NULL           |                |
 * rev_user          | int(10) unsigned    | NO   | MUL | 0              |                |
 * rev_user_text     | varbinary(255)      | NO   | MUL |                |                |
 * rev_timestamp     | binary(14)          | NO   | MUL |                |                |
 * rev_minor_edit    | tinyint(3) unsigned | NO   |     | 0              |                |
 * rev_deleted       | tinyint(3) unsigned | NO   |     | 0              |                |
 * rev_len           | int(10) unsigned    | YES  |     | NULL           |                |
 * rev_parent_id     | int(10) unsigned    | YES  |     | NULL           |                |
 * rev_sha1          | varbinary(32)       | NO   |     |                |                |
 * rev_content_model | varbinary(32)       | YES  |     | NULL           |                |
 * rev_content_format | varbinary(64)      | YES  |     | NULL           |                |

DESCRIBE revision in MediaWiki 1.19 gives the following: mysql> describe mw_revision; ++-+--+-+-++ ++-+--+-+-++ ++-+--+-+-++ 12 rows in set
 * Field         | Type                | Null | Key | Default | Extra          |
 * rev_id        | int(8) unsigned     | NO   | PRI | NULL    | auto_increment |
 * rev_page      | int(8) unsigned     | NO   | PRI | NULL    |                |
 * rev_text_id   | int(8) unsigned     | NO   |     | NULL    |                |
 * rev_comment   | tinyblob            | NO   |     | NULL    |                |
 * rev_user      | int(5) unsigned     | NO   | MUL | 0       |                |
 * rev_user_text | varbinary(255)      | NO   | MUL | NULL    |                |
 * rev_timestamp | binary(14)          | NO   | MUL | NULL    |                |
 * rev_minor_edit | tinyint(1) unsigned | NO  |     | 0       |                |
 * rev_deleted   | tinyint(1) unsigned | NO   |     | 0       |                |
 * rev_len       | int(8) unsigned     | YES  |     | NULL    |                |
 * rev_parent_id | int(8) unsigned     | YES  |     | NULL    |                |
 * rev_sha1      | varbinary(32)       | NO   |     | NULL    |                |

DESCRIBE revision in MediaWiki 1.18 gives the following: mysql> describe mw_revision; ++-+--+-+-++ ++-+--+-+-++ ++-+--+-+-++ 11 rows in set
 * Field         | Type                | Null | Key | Default | Extra          |
 * rev_id        | int(8) unsigned     | NO   | PRI | NULL    | auto_increment |
 * rev_page      | int(8) unsigned     | NO   | PRI | NULL    |                |
 * rev_text_id   | int(8) unsigned     | NO   |     | NULL    |                |
 * rev_comment   | tinyblob            | NO   |     | NULL    |                |
 * rev_user      | int(5) unsigned     | NO   | MUL | 0       |                |
 * rev_user_text | varbinary(255)      | NO   | MUL | NULL    |                |
 * rev_timestamp | binary(14)          | NO   | MUL | NULL    |                |
 * rev_minor_edit | tinyint(1) unsigned | NO  |     | 0       |                |
 * rev_deleted   | tinyint(1) unsigned | NO   |     | 0       |                |
 * rev_len       | int(8) unsigned     | YES  |     | NULL    |                |
 * rev_parent_id | int(8) unsigned     | YES  |     | NULL    |                |

DESCRIBE revision in MediaWiki 1.11 gives the following: mysql> describe mw_revision; ++-+--+-+-++ ++-+--+-+-++ ++-+--+-+-++ 11 rows in set
 * Field         | Type                | Null | Key | Default | Extra          |
 * rev_id        | int(8) unsigned     | NO   | PRI | NULL    | auto_increment |
 * rev_page      | int(8) unsigned     | NO   | PRI | NULL    |                |
 * rev_text_id   | int(8) unsigned     | NO   |     | NULL    |                |
 * rev_comment   | tinyblob            | NO   |     | NULL    |                |
 * rev_user      | int(5) unsigned     | NO   | MUL | 0       |                |
 * rev_user_text | varchar(255)        | NO   | MUL | NULL    |                |
 * rev_timestamp | char(14)            | NO   | MUL | NULL    |                |
 * rev_minor_edit | tinyint(1) unsigned | NO  |     | 0       |                |
 * rev_deleted   | tinyint(1) unsigned | NO   |     | 0       |                |
 * rev_len       | int(8) unsigned     | YES  |     | NULL    |                |
 * rev_parent_id | int(8) unsigned     | YES  |     | NULL    |                |

DESCRIBE revision in MediaWiki 1.9 and earlier gives the following: mysql> describe mw_revision; ++-+--+-+-++ ++-+--+-+-++ ++-+--+-+-++ 9 rows in set
 * Field         | Type                | Null | Key | Default | Extra          |
 * rev_id        | int(8) unsigned     | NO   | PRI | NULL    | auto_increment |
 * rev_page      | int(8) unsigned     | NO   | PRI | NULL    |                |
 * rev_text_id   | int(8) unsigned     | NO   |     | NULL    |                |
 * rev_comment   | tinyblob            | NO   |     | NULL    |                |
 * rev_user      | int(5) unsigned     | NO   | MUL | 0       |                |
 * rev_user_text | varchar(255)        | NO   | MUL | NULL    |                |
 * rev_timestamp | char(14)            | NO   | MUL | NULL    |                |
 * rev_minor_edit | tinyint(1) unsigned | NO  |     | 0       |                |
 * rev_deleted   | tinyint(1) unsigned | NO   |     | 0       |                |

Indices
mysql> show index in revision; +--++-+--+---+---+-+--++--++-+---+ +--++-+--+---+---+-+--++--++-+---+ +--++-+--+---+---+-+--++--++-+---+ 13 rows in set (0.02 sec)
 * Table   | Non_unique | Key_name            | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
 * revision |         0 | PRIMARY             |            1 | rev_id        | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
 * revision |         0 | rev_page_id         |            1 | rev_page      | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
 * revision |         0 | rev_page_id         |            2 | rev_id        | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
 * revision |         1 | rev_timestamp       |            1 | rev_timestamp | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
 * revision |         1 | page_timestamp      |            1 | rev_page      | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
 * revision |         1 | page_timestamp      |            2 | rev_timestamp | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
 * revision |         1 | user_timestamp      |            1 | rev_user      | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
 * revision |         1 | user_timestamp      |            2 | rev_timestamp | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
 * revision |         1 | usertext_timestamp  |            1 | rev_user_text | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
 * revision |         1 | usertext_timestamp  |            2 | rev_timestamp | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
 * revision |         1 | page_user_timestamp |            1 | rev_page      | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
 * revision |         1 | page_user_timestamp |            2 | rev_user      | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
 * revision |         1 | page_user_timestamp |            3 | rev_timestamp | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |