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
• changetagdef - 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
• userformergroups - Former group memberships
• userautocreateserial - Auto-created user sequence
• watchlist - User watchlist entries
• watchlist_expiry - Watchlist expiry information
• watchlist_label - Custom watchlist labels
• watchlistlabelmember - 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 quer

…

## Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

- **Author:** [santhoshtr](https://github.com/santhoshtr)
- **Source:** [santhoshtr/wiki-skills](https://github.com/santhoshtr/wiki-skills)
- **License:** MIT

Install and usage instructions live in the source repository linked above.