# Mediawiki Database Tables

> Master MediaWiki database schema and write optimized queries. Covers all 64 core tables with field definitions, indexes, relationships, and query optimization techniques. Includes replica vs primary strategies, JOIN patterns, pagination, caching, and 50+ real-world examples for Wikimedia/MediaWiki development.

- Skill: `santhoshtr/mediawiki-database-tables` (Agent Skill, multi-file: 5 files)
- Install (CLI): `npx skillmds@latest add santhoshtr/mediawiki-database-tables`
- Raw SKILL.md: https://api.skillmd.com/api/skills/santhoshtr/mediawiki-database-tables/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Coding & Dev Tools
- Author: santhoshtr (https://skillmd.com/u/santhoshtr)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/santhoshtr/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

```php
// 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

```php
// 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

```php
// 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

```php
$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

```php
$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:**

```php
// 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

```php
// 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:**

```php
// 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**

```php
$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**

```php
$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**

```php
$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**

```php
$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**

```php
$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

```php
// 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

```php
// 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

```php
// 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

```php
// 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

```php
// 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

```php
// 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

```php
// 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

```php
// 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

```php
// 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

```php
// 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:

```php
// 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](https://www.mediawiki.org/wiki/Manual:Database_layout)
- [MediaWiki Manual: Database Optimization](https://www.mediawiki.org/wiki/Database_optimization)
- [MediaWiki Manual: Database Access](https://www.mediawiki.org/wiki/Manual:Database_access)
- [IDatabase API Documentation](https://phabricator.wikimedia.org/source/mediawiki/browse/HEAD/includes/libs/rdbms/database/IDatabase.php)

## 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)