Mediawiki Database Tables

Who This Skill Is For

• Wikimedia developers - Building features for Wikipedia and sister projects
• Extension developers - Creating MediaWiki extensions that access the database
• Data analysts - Running queries against wiki databases
• System administrators - Understanding wiki data architecture
• Researchers - Analyzing wiki activity and content

Quick Start

Get a Database Connection

// For READ operations (use replicas)
$services = MediaWikiServices::getInstance();
$dbr = $services->getDBLoadBalancer()->getConnection( DB_REPLICA );

// For WRITE operations (use primary)
$dbw = $services->getDBLoadBalancer()->getConnection( DB_PRIMARY );

Basic Query Pattern

// Simple SELECT with WHERE and LIMIT
$result = $dbr->select(
    'page',                           // table
    [ 'page_id', 'page_title' ],     // fields to select
    [ 'page_namespace' => 0 ],       // WHERE conditions
    __METHOD__,                       // method name for logging
    [ 'LIMIT' => 10 ]                // options
);

// Process results
foreach ( $result as $row ) {
    echo $row->page_title . "\n";
}

Join Example

// Get pages with their latest revision timestamp
$result = $dbr->select(
    [ 'page', 'revision' ],
    [ 'page_id', 'page_title', 'rev_timestamp' ],
    [ 'page_namespace' => 0 ],
    __METHOD__,
    [],
    [ 'revision' => [ 'LEFT JOIN', 'page_id = rev_page AND rev_id = page_latest' ] ]
);

Insert Example

$dbw->insert(
    'page',
    [
        'page_namespace' => 0,
        'page_title' => 'New_Page',
        'page_is_redirect' => 0,
        'page_latest' => 1,
        'page_len' => 100,
        'page_random' => wfRandom()
    ],
    __METHOD__
);

Table Organization

MediaWiki's 64 tables are organized into logical categories:

Core Content

• page - Wiki pages
• revision - Page revisions
• slots - Content slots (Modular Content Representation)
• content - Actual content storage
• text - Legacy content storage (deprecated)

User & Authentication

• user - User accounts
• actor - User/IP attribution system
• user_groups - Group membership
• user_properties - User preferences and settings
• bot_passwords - Bot login credentials

Links & References

• pagelinks - Internal page-to-page links
• templatelinks - Template transclusions
• imagelinks - Image usage
• categorylinks - Category membership
• externallinks - External URLs linked from pages
• iwlinks - Interwiki links
• langlinks - Language links
• linktarget - Normalized link targets

Files & Media

• image - Current file uploads
• oldimage - Previous file versions
• file - MCR file information
• filerevision - File version metadata
• filearchive - Deleted files

Logging & Changes

• logging - Action logs (move, delete, protect, etc.)
• recentchanges - Recent changes feed
• archive - Deleted revisions
• log_search - Log search index

Metadata & Properties

• page_props - Page properties
• category - Category pages
• redirect - Page redirects
• page_restrictions - Page protection
• protected_titles - Protected/reserved titles
• change_tag - Edit tags
• change_tag_def - Tag definitions

Search & Performance

• searchindex - Full-text search index
• objectcache - General cache storage
• querycache - Cached query results
• querycachetwo - Additional cached queries
• l10n_cache - Localization cache

User Management

• user_newtalk - "New talk messages" flag
• user_former_groups - Former group memberships
• user_autocreate_serial - Auto-created user sequence
• watchlist - User watchlist entries
• watchlist_expiry - Watchlist expiry information
• watchlist_label - Custom watchlist labels
• watchlist_label_member - Label memberships

Blocks & Restrictions

• block - User/IP blocks
• block_target - Block target information
• ipblocks_restrictions - Page-specific block restrictions

Comments & Text

• comment - Comment storage (normalized)

System & Configuration

• job - Job queue entries
• sites - Configured sites (for multi-wiki)
• site_identifiers - Site identifiers
• site_stats - Wiki statistics
• interwiki - Interwiki prefixes
• updatelog - Schema update log
• uploadstash - Temporary upload staging
• collation - Collation information
• content_models - Content model types
• slot_roles - Content slot roles

Core Workflows

Workflow 1: Understanding Table Structure

Goal: Find the right table for your data and understand what it contains.

Steps:

1. Identify your data type - Are you working with pages, users, revisions, logs, files?
2. Reference the schema - Look up the table in references/schema-complete.md
3. Understand the fields - Each table document lists all fields with descriptions
4. Check the indexes - Understand what lookups will be efficient
5. Find related tables - See what other tables contain related data

Example: You need to find the page ID for a specific wiki page.

Data type: A wiki page
Table: page
Fields needed: page_id, page_namespace, page_title
Index to use: page_name_title (unique index on namespace + title)

Why: Pages are uniquely identified by namespace + title, not title alone.
The page_name_title index makes this lookup very fast.

Best Practices:

• Always check references/schema-complete.md before writing queries
• Look at the indexes to understand fast vs slow lookups
• Note any deprecated tables (like text)
• Pay attention to visibility flags (*_deleted fields)

Workflow 2: Writing Optimized SELECT Queries

Goal: Write queries that use indexes efficiently and return only needed data.

Steps:

1. Choose replica vs primary - Use replicas for reads
2. Select only needed columns - Never use SELECT *
3. Use indexed columns in WHERE - Check what indexes exist
4. Add LIMIT for safety - Always limit results
5. Test with EXPLAIN - Verify index usage

Example: Get recently edited pages in the main namespace

$result = $dbr->select(
    'page',
    [ 'page_id', 'page_title', 'page_touched' ],  // Only needed columns
    [
        'page_namespace' => 0,                      // Use indexed column
        'page_touched >= ' . $dbr->addQuotes(
            wfTimestamp( TS_MW, time() - 86400 )  // Last 24 hours
        )
    ],
    __METHOD__,
    [
        'ORDER BY' => 'page_touched DESC',
        'LIMIT' => 100                             // Always limit
    ]
);

Performance Tips:

• Use indexed columns in WHERE clauses - Check schema-complete.md for indexes
• Avoid functions on indexed columns - WHERE YEAR(timestamp) = 2024 won't use index
• Use LIMIT to reduce data transfer - Not just for safety, but performance
• SELECT specific columns - Reduces memory, network, disk I/O
• Order by indexed columns when possible

Common Anti-Patterns to Avoid:

• SELECT * on large tables - wastes resources
• No WHERE clause on large tables - full table scan
• WHERE on non-indexed columns - slow
• No LIMIT - risk of returning huge datasets
• LIMIT with OFFSET > 1000 - very slow

Workflow 3: Choosing Replica vs Primary Database

Goal: Use the right database connection for your operation.

Decision Tree:

Are you reading data?
├─ Yes, will read immediately after writing in same request?
│  └─ Use PRIMARY (replica lag consideration)
├─ Yes, just reading without writing?
│  └─ Use REPLICA (DB_REPLICA)
└─ No, you're writing/updating?
   └─ Use PRIMARY (DB_PRIMARY)

In a transaction?
└─ Always use PRIMARY (keep transaction on same connection)

Why Replicas?

• Wikipedia and major wikis have read replicas for load distribution
• Replicas can lag 1-5 seconds behind the primary
• Use replicas for background jobs, analysis, bulk reads

Why Primary?

• Write operations must go to the primary (source of truth)
• Consistency when reading immediately after writing
• Transactions must be on the same connection

Code Examples:

// Correct: Read from replica
$dbr = $services->getDBLoadBalancer()->getConnection( DB_REPLICA );
$row = $dbr->selectRow( 'page', '*', [ 'page_id' => 1 ] );

// Correct: Write to primary
$dbw = $services->getDBLoadBalancer()->getConnection( DB_PRIMARY );
$dbw->insert( 'page', $pageData );

// Correct: Read immediately after write (same connection)
$dbw = $services->getDBLoadBalancer()->getConnection( DB_PRIMARY );
$dbw->insert( 'page', $pageData );
$newRow = $dbw->selectRow( 'page', '*', [ 'page_id' => $newId ] );

// WRONG: Writing to replica
$dbr = $services->getDBLoadBalancer()->getConnection( DB_REPLICA );
$dbr->insert( 'page', $pageData );  // ERROR!

// WRONG: Assuming immediate replica consistency
$dbw->insert( 'page', $pageData );
$dbr = $services->getDBLoadBalancer()->getConnection( DB_REPLICA );
$row = $dbr->selectRow( 'page', '*', [ 'page_id' => $newId ] );  // May not exist yet!

Workflow 4: Joining Tables Correctly

Goal: Combine data from multiple tables efficiently.

Steps:

1. Understand the relationship - How are the tables connected?
2. Know the join conditions - What fields should match?
3. Check indexes on join columns - All sides should be indexed
4. Start with the smallest table - Order matters for performance
5. Use LEFT JOIN for optional data - INNER JOIN for required data

Example: Get a user's contributions with page titles

// Join: user → actor → revision → page
$result = $dbr->select(
    [ 'actor', 'revision', 'page' ],
    [ 'actor_name', 'rev_timestamp', 'page_namespace', 'page_title' ],
    [ 'actor_name' => 'Example' ],
    __METHOD__,
    [ 'ORDER BY' => 'rev_timestamp DESC', 'LIMIT' => 50 ],
    [
        'revision' => [ 'INNER JOIN', 'actor_id = rev_actor' ],
        'page' => [ 'INNER JOIN', 'rev_page = page_id' ]
    ]
);

Common Join Patterns:

1. Page to revisions - page_id = rev_page
2. Page to links - page_namespace, page_title match link target
3. Revision to content - rev_id = slot_revision_id → slot_content_id = content_id
4. User to actor - user_id = actor_user
5. Actor to attribution - actor_id = rev_actor or log_actor

See: references/table-relationships.md for more join patterns.

Workflow 5: Analyzing Query Performance

Goal: Identify slow queries and understand why they're slow.

Steps:

1. Run EXPLAIN - See how MySQL executes the query
2. Check row counts - Is it scanning too many rows?
3. Look for index usage - Are indexes being used?
4. Identify full table scans - type = "ALL" means scanning all rows
5. Optimize based on findings - Add indexes, change WHERE clauses, add LIMIT

EXPLAIN Example:

// Run EXPLAIN on your query
$dbr = $services->getDBLoadBalancer()->getConnection( DB_REPLICA );

// Get the query
$query = $dbr->selectQueryBuilder()
    ->select( [ 'page_id', 'page_title' ] )
    ->from( 'page' )
    ->where( [ 'page_namespace' => 0, 'page_is_redirect' => 0 ] )
    ->limit( 100 )
    ->getSQL();

// Run EXPLAIN on it
$explainResult = $dbr->query( "EXPLAIN " . $query );

// Check the type column:
// - "const" = one row (best)
// - "ref" = index lookup (good)
// - "range" = index range scan (okay)
// - "ALL" = full table scan (bad)

What to Look For:

• key column - Which index is used? (NULL means no index)
• type column - How is the table accessed?
• rows column - Approximate rows examined
• filtered column - % of rows passing WHERE clause

Optimization Strategies:

• If type = ALL and you have a WHERE, add an index on the WHERE column
• If rows is very high, add LIMIT or more specific WHERE conditions
• If filtered is low, your WHERE clause is inefficient
• Join order matters: put most-selective table first

Workflow 6: Common Query Patterns

Goal: Use proven query patterns for common tasks.

Common Patterns:

1. Get a page by title

$page = $dbr->selectRow(
    'page',
    [ 'page_id', 'page_latest', 'page_len' ],
    [ 'page_namespace' => 0, 'page_title' => 'Main_Page' ],
    __METHOD__
);

2. Get recent changes to a page

$revisions = $dbr->select(
    [ 'revision', 'actor' ],
    [ 'rev_id', 'rev_timestamp', 'actor_name' ],
    [ 'rev_page' => $pageId ],
    __METHOD__,
    [ 'ORDER BY' => 'rev_timestamp DESC', 'LIMIT' => 20 ],
    [ 'actor' => [ 'JOIN', 'rev_actor = actor_id' ] ]
);

3. Get user contributions

$contributions = $dbr->select(
    [ 'actor', 'revision', 'page' ],
    [ 'rev_timestamp', 'page_namespace', 'page_title', 'rev_minor_edit' ],
    [ 'actor_name' => $username ],
    __METHOD__,
    [ 'ORDER BY' => 'rev_timestamp DESC', 'LIMIT' => 50 ],
    [
        'revision' => [ 'JOIN', 'actor_id = rev_actor' ],
        'page' => [ 'JOIN', 'rev_page = page_id' ]
    ]
);

4. Get pages in a category

$pages = $dbr->select(
    [ 'categorylinks', 'page' ],
    [ 'page_id', 'page_namespace', 'page_title' ],
    [ 'cl_to' => $categoryTitle ],
    __METHOD__,
    [ 'LIMIT' => 100 ],
    [ 'page' => [ 'JOIN', 'cl_from = page_id' ] ]
);

5. Get all pages linking to a target

$links = $dbr->select(
    [ 'pagelinks', 'page' ],
    [ 'page_namespace', 'page_title' ],
    [],
    __METHOD__,
    [ 'LIMIT' => 100 ],
    [
        'page' => [ 'JOIN', 'pl_from = page_id' ],
        // Filter by target - use linktarget table
    ]
);

See: references/common-tables.md for detailed examples of each table.

Critical Best Practices

1. Use Replicas for Reads

// Good
$dbr = $services->getDBLoadBalancer()->getConnection( DB_REPLICA );
$result = $dbr->select( 'page', '*', [] );

// Avoid
$dbw = $services->getDBLoadBalancer()->getConnection( DB_PRIMARY );
$result = $dbw->select( 'page', '*', [] );  // Unnecessary primary load

2. Use Primary for Writes

// Good
$dbw = $services->getDBLoadBalancer()->getConnection( DB_PRIMARY );
$dbw->insert( 'page', $data );

// Avoid
$dbr = $services->getDBLoadBalancer()->getConnection( DB_REPLICA );
$dbr->insert( 'page', $data );  // Will fail - replicas are read-only

3. SELECT Specific Columns

// Good
$dbr->select( 'page', [ 'page_id', 'page_title' ], [] );

// Avoid
$dbr->select( 'page', '*', [] );  // Wastes memory and bandwidth

// Avoid
$dbr->select( 'page', [ '*' ], [] );  // Same as above

4. Use Indexed Columns in WHERE

// Good (uses index)
$dbr->select( 'page', '*', [ 'page_namespace' => 0, 'page_title' => 'Test' ] );

// Avoid (no index on page_touched for this query)
$dbr->select( 'page', '*', [ 'page_touched > ' . time() - 86400 ] );

// Better (add index or avoid condition)
$dbr->select( 'page', '*', [ 'page_is_redirect' => 0 ], __METHOD__, [ 'LIMIT' => 100 ] );

5. Use LIMIT

// Good (safe limit)
$dbr->select( 'page', '*', [], __METHOD__, [ 'LIMIT' => 100 ] );

// Avoid (no limit - could get millions of rows)
$dbr->select( 'page', '*', [] );

// Avoid (very large limit)
$dbr->select( 'page', '*', [], __METHOD__, [ 'LIMIT' => 1000000 ] );

6. Avoid Functions on Indexed Columns

// Good (uses index)
$dbr->select( 'revision', '*', [ 'rev_timestamp >= ' . $cutoff ] );

// Avoid (function prevents index usage)
$dbr->select( 'revision', '*', [ 'YEAR(rev_timestamp) = 2024' ] );

// Good alternative
$start = wfTimestamp( TS_MW, mktime( 0, 0, 0, 1, 1, 2024 ) );
$end = wfTimestamp( TS_MW, mktime( 0, 0, 0, 1, 1, 2025 ) );
$dbr->select( 'revision', '*', [
    'rev_timestamp >= ' . $start,
    'rev_timestamp < ' . $end
] );

7. Use Keyset Pagination, Not OFFSET

// Slow (OFFSET scans all rows up to the offset)
// SELECT * FROM page LIMIT 10 OFFSET 5000;  // Scans 5010 rows!
$dbr->select( 'page', '*', [], __METHOD__,
    [ 'LIMIT' => 10, 'OFFSET' => 5000 ]
);

// Fast (keyset pagination - only scans needed rows)
// SELECT * FROM page WHERE page_id > ? LIMIT 10;
$dbr->select( 'page', '*', [ 'page_id >' . $lastSeenId ], __METHOD__,
    [ 'LIMIT' => 10, 'ORDER BY' => 'page_id' ]
);

8. Never Assume Table Prefixes

// Good (uses proper table naming)
$dbr->select( 'page', '*', [] );

// Also good (explicit table name)
$dbr->select( $dbr->tableName( 'page' ), '*', [] );

// Avoid (hardcoding prefix)
$dbr->select( 'wiki_page', '*', [] );  // What if prefix is different?

9. Check for Deleted/Suppressed Content

// Good (exclude deleted revisions)
$dbr->select( 'revision', '*', [ 'rev_deleted' => 0 ] );

// Good (include all, then check in PHP)
$result = $dbr->select( 'revision', [ 'rev_id', 'rev_deleted' ], [] );
foreach ( $result as $row ) {
    if ( $row->rev_deleted ) continue;  // Skip deleted
    // Process row
}

// Deleted flags exist on many tables:
// - revision: rev_deleted
// - archive: ar_deleted
// - comment: comment_data (for suppressed text)
// - file: img_deleted, oi_deleted

10. Always Include METHOD in Queries

// Good (includes method name for logging)
$dbr->select( 'page', '*', [], __METHOD__ );

// Less helpful (no method context)
$dbr->select( 'page', '*', [] );

Understanding Table Relationships

MediaWiki's normalized design means understanding how tables connect is crucial.

Key Relationships:

• page ↔ revision - Page has many revisions (page_id = rev_page)
• revision ↔ actor - Revision has one author (rev_actor = actor_id)
• actor ↔ user - Actor represents a user (actor_user = user_id)
• revision ↔ slots ↔ content - Revision stores content via slots
• page ↔ categorylinks - Page is in categories (page_id = cl_from)
• page ↔ pagelinks - Page links to others (page_id = pl_from)
• page ↔ redirect - Redirect target (page_id = rd_from)

See: references/table-relationships.md for visual diagrams and more examples.

Deprecated Tables

Some tables are deprecated and should be avoided in new code:

• text - Use content table instead (part of Modular Content Representation)
• oldimage - For file history, but prefer filerevision table
• archive - Only for viewing deleted content, not for active data

Caching

Many queries can be cached to improve performance:

// Cache a query result
$cache = MediaWikiServices::getInstance()->getMainWANObjectCache();
$key = $cache->makeKey( 'page', 'title', $title );

$row = $cache->getWithSetCallback(
    $key,
    3600,  // Cache for 1 hour
    function() use ( $dbr, $title ) {
        return $dbr->selectRow( 'page', '*', [ 'page_title' => $title ] );
    }
);

Reference Files

This skill includes detailed reference documentation:

• schema-complete.md - All 64 tables with complete field definitions, indexes, and descriptions
• optimization-guide.md - In-depth optimization techniques and patterns
• common-tables.md - Deep dive into the 20 most-used tables with examples
• table-relationships.md - How tables connect and common join patterns

Common Pitfalls

1. Forgetting about redirects - Check page_is_redirect flag
2. Using page title without namespace - Use page_namespace + page_title
3. Ignoring visibility flags - Always check *_deleted fields
4. Joining to deprecated tables - Use content not text
5. Not understanding actor system - Actors normalize user attribution
6. Querying primary unnecessarily - Use replicas for reads
7. SELECT * - Always select specific columns
8. N+1 queries - Use joins instead of loops
9. OFFSET pagination - Use keyset pagination for large datasets
10. Assuming consistency - Replicas lag behind primary

Next Steps

1. Read references/schema-complete.md to understand the tables
2. Review references/table-relationships.md to see how they connect
3. Check references/common-tables.md for examples of common queries
4. Study references/optimization-guide.md for performance techniques
5. Practice writing queries against your local MediaWiki installation

Additional Resources

• MediaWiki Manual: Database Layout
• MediaWiki Manual: Database Optimization
• MediaWiki Manual: Database Access
• IDatabase API Documentation

Questions?

If you encounter queries that don't work as expected, check:

1. Are you using the right table? (See schema-complete.md)
2. Are the columns you're accessing indexed? (See schema-complete.md)
3. Are you understanding the table relationships? (See table-relationships.md)
4. Are there specific examples for this query? (See common-tables.md)
5. Are you following optimization best practices? (See optimization-guide.md)