Mediawiki Database Tables

MediaWiki Database Tables

Master the MediaWiki database schema and write optimized queries for wiki data. This skill provides comprehensive documentation of all 64 core database tables, relationships, and best practices for querying wiki data efficiently.

What You'll Learn

• How the MediaWiki database is structured and organized
• How to find the right table for your data
• How to write efficient queries that use indexes properly
• Best practices for reading from replicas and writing to primary database
• Common query patterns and anti-patterns
• How tables relate to each other and when to use joins
• Query optimization techniques specific to MediaWiki

When to Use This Skill

Use this skill when you need to:

• Write database queries for MediaWiki/Wikimedia extensions
• Understand the schema for a feature you're building
• Optimize slow queries that interact with wiki data
• Analyze wiki data for research or reporting
• Debug database-related issues in extensions
• Understand table relationships for complex queries
• Learn MediaWiki conventions for database access

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)