Back to Articles
15 min read

WordPress Database Engineering: The wpdb Class, Custom Tables & Performance

When standard Post Types fail to scale, direct database interaction is required. This technical reference covers the `$wpdb` abstraction layer for executing raw SQL, managing schema migrations and versioning via `dbDelta`, and strategies for indexing and query analysis to ensure high-performance data retrieval.

wordpress

wpdb Class

Global $wpdb object

The $wpdb object is WordPress's database abstraction layer instantiated globally, providing safe methods to interact with MySQL. You must declare it global inside functions to access it, as it's not automatically available in local scope.

function my_custom_query() {
    global $wpdb;  // Required to access the object
    // Now you can use $wpdb->...
}

Table Name Properties

WordPress provides built-in properties for core table names, ensuring compatibility across installations with different prefixes. These properties return the full prefixed table name.

global $wpdb;
$wpdb->posts        // wp_posts
$wpdb->postmeta     // wp_postmeta
$wpdb->users        // wp_users
$wpdb->usermeta     // wp_usermeta
$wpdb->comments     // wp_comments
$wpdb->terms        // wp_terms
$wpdb->options      // wp_options

Prefix Handling

Table prefixes (default wp_) allow multiple WordPress installations in one database and add a layer of security. Always use the prefix property rather than hardcoding to ensure portability.

┌─────────────────────────────────────────┐
│  Database: wordpress_db                 │
├─────────────────────────────────────────┤
│  wp_posts      (site 1)                 │
│  wp_options    (site 1)                 │
│  blog2_posts   (site 2, prefix=blog2_)  │
│  blog2_options (site 2)                 │
└─────────────────────────────────────────┘

$wpdb->prefix

The $wpdb->prefix property returns the current installation's table prefix string, essential when creating custom tables or writing raw SQL queries to maintain compatibility.

global $wpdb;
$table = $wpdb->prefix . 'my_custom_table';  // wp_my_custom_table

// For multisite base prefix (without blog ID)
$base_table = $wpdb->base_prefix . 'global_data';

Database Queries

$wpdb->get_results()

Returns an entire result set as an array of objects (default), associative arrays, or numeric arrays. Use for queries expecting multiple rows.

global $wpdb;
// Returns array of objects
$posts = $wpdb->get_results(
    "SELECT ID, post_title FROM {$wpdb->posts} WHERE post_status = 'publish' LIMIT 10"
);

// Output types: OBJECT (default), OBJECT_K, ARRAY_A, ARRAY_N
$posts = $wpdb->get_results($query, ARRAY_A);  // Associative array

foreach ($posts as $post) {
    echo $post->post_title;  // Object access
}

$wpdb->get_row()

Retrieves a single row from the database, returning an object by default. Useful when you expect exactly one result, like fetching a specific record by ID.

global $wpdb;
$user = $wpdb->get_row(
    $wpdb->prepare(
        "SELECT * FROM {$wpdb->users} WHERE ID = %d",
        $user_id
    )
);

echo $user->user_email;

// Optional: specify row offset and output type
$row = $wpdb->get_row($query, ARRAY_A, 0);

$wpdb->get_col()

Returns a single column from multiple rows as a simple one-dimensional array. Perfect for fetching a list of IDs or single values from many records.

global $wpdb;
$post_ids = $wpdb->get_col(
    "SELECT ID FROM {$wpdb->posts} WHERE post_type = 'post'"
);

// Result: array(1, 5, 12, 23, 45...)

// Second parameter: column offset (0-indexed)
$titles = $wpdb->get_col($query, 1);  // Get second column

$wpdb->get_var()

Returns a single scalar value from the database—one column from one row. Ideal for COUNT queries, single field lookups, or aggregate functions.

global $wpdb;
$count = $wpdb->get_var(
    "SELECT COUNT(*) FROM {$wpdb->posts} WHERE post_status = 'publish'"
);

echo "Published posts: $count";

// Can specify row and column offsets
$value = $wpdb->get_var($query, 0, 2);  // Column 0, Row 2

$wpdb->query()

Executes any SQL query and returns the number of affected rows. Use for non-SELECT queries (UPDATE, DELETE) or when other methods don't fit your needs.

global $wpdb;
$rows_affected = $wpdb->query(
    $wpdb->prepare(
        "UPDATE {$wpdb->postmeta} SET meta_value = %s WHERE meta_key = %s",
        'new_value',
        'my_meta_key'
    )
);

// Returns: number of rows affected, or false on error
// For SELECT: use get_results() instead

$wpdb->insert()

Safely inserts a new row into a database table using an associative array of column-value pairs. Handles escaping automatically and returns false on failure.

global $wpdb;
$wpdb->insert(
    $wpdb->prefix . 'my_table',      // Table name
    array(                            // Data
        'column1' => 'value1',
        'column2' => 123,
        'created' => current_time('mysql')
    ),
    array('%s', '%d', '%s')          // Format (optional)
);

$new_id = $wpdb->insert_id;  // Get auto-increment ID

$wpdb->update()

Updates existing rows matching a WHERE clause using associative arrays for both data and conditions. Returns the number of rows updated or false on error.

global $wpdb;
$wpdb->update(
    $wpdb->prefix . 'my_table',      // Table
    array('status' => 'active'),     // Data to update
    array('user_id' => 5),           // WHERE conditions
    array('%s'),                      // Data format
    array('%d')                       // WHERE format
);
┌─────────────────────────────────────────────┐
│  UPDATE wp_my_table                         │
│  SET status = 'active'                      │
│  WHERE user_id = 5                          │
└─────────────────────────────────────────────┘

$wpdb->delete()

Removes rows from a table matching the specified conditions. Returns the number of deleted rows or false on error—always use conditions to avoid deleting everything!

global $wpdb;
$rows_deleted = $wpdb->delete(
    $wpdb->prefix . 'my_table',
    array(
        'user_id' => 5,
        'status'  => 'expired'
    ),
    array('%d', '%s')
);

// Equivalent: DELETE FROM wp_my_table WHERE user_id = 5 AND status = 'expired'

$wpdb->replace()

Inserts a new row or replaces an existing one if a duplicate key exists. Works like INSERT with ON DUPLICATE KEY behavior—useful for upsert operations.

global $wpdb;
$wpdb->replace(
    $wpdb->prefix . 'my_table',
    array(
        'id'     => 5,           // If exists, row is replaced
        'name'   => 'Updated',
        'value'  => 100
    ),
    array('%d', '%s', '%d')
);

// Note: REPLACE deletes then inserts, triggering DELETE triggers

Custom Tables

dbDelta() Function

The dbDelta() function intelligently creates or modifies tables by comparing your schema definition with the existing table structure. It requires precise formatting including two spaces after PRIMARY KEY.

require_once ABSPATH . 'wp-admin/includes/upgrade.php';

function create_my_table() {
    global $wpdb;
    $table = $wpdb->prefix . 'my_custom_table';
    $charset = $wpdb->get_charset_collate();
    
    $sql = "CREATE TABLE $table (
        id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
        user_id bigint(20) UNSIGNED NOT NULL,
        data text NOT NULL,
        created datetime DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY  (id),
        KEY user_id (user_id)
    ) $charset;";
    
    dbDelta($sql);
}
register_activation_hook(__FILE__, 'create_my_table');

Table Creation

Custom tables should be created during plugin activation using a hook, with proper checks to avoid recreation. Always include charset/collation for compatibility.

register_activation_hook(__FILE__, 'my_plugin_activate');

function my_plugin_activate() {
    create_my_table();
    add_option('my_plugin_db_version', '1.0');
}

register_deactivation_hook(__FILE__, 'my_plugin_deactivate');

function my_plugin_deactivate() {
    // Optionally clean up (usually keep data)
}

register_uninstall_hook(__FILE__, 'my_plugin_uninstall');

function my_plugin_uninstall() {
    global $wpdb;
    $wpdb->query("DROP TABLE IF EXISTS {$wpdb->prefix}my_custom_table");
    delete_option('my_plugin_db_version');
}

Table Schema

Design your schema following WordPress conventions: use bigint(20) UNSIGNED for IDs, datetime for timestamps, and match data types with related core tables for foreign key compatibility.

┌────────────────────────────────────────────────────────┐
│  Table: wp_booking_entries                             │
├──────────────┬─────────────────────┬──────────────────┤
│  Column      │  Type               │  Attributes      │
├──────────────┼─────────────────────┼──────────────────┤
│  id          │  bigint(20)         │  PK, AUTO_INC    │
│  user_id     │  bigint(20)         │  FK → wp_users   │
│  post_id     │  bigint(20)         │  FK → wp_posts   │
│  status      │  varchar(20)        │  INDEX           │
│  amount      │  decimal(10,2)      │                  │
│  meta        │  longtext           │  JSON data       │
│  created_at  │  datetime           │                  │
│  updated_at  │  datetime           │                  │
└──────────────┴─────────────────────┴──────────────────┘

Foreign Keys

WordPress core doesn't use foreign keys for flexibility, but you can add them for data integrity. Note: dbDelta() doesn't handle FK constraints well—add them separately.

function add_foreign_keys() {
    global $wpdb;
    
    // Add FK after table creation
    $wpdb->query("
        ALTER TABLE {$wpdb->prefix}my_orders
        ADD CONSTRAINT fk_user_id 
        FOREIGN KEY (user_id) 
        REFERENCES {$wpdb->users}(ID) 
        ON DELETE CASCADE
    ");
}

// Check if constraint exists first
$constraints = $wpdb->get_results("
    SELECT * FROM information_schema.TABLE_CONSTRAINTS 
    WHERE CONSTRAINT_NAME = 'fk_user_id'
");

Indexes

Indexes dramatically speed up queries on large tables. Add indexes on columns used in WHERE, JOIN, and ORDER BY clauses, but avoid over-indexing as it slows writes.

$sql = "CREATE TABLE {$wpdb->prefix}logs (
    id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id bigint(20) UNSIGNED NOT NULL,
    action varchar(50) NOT NULL,
    object_type varchar(50),
    object_id bigint(20) UNSIGNED,
    created_at datetime NOT NULL,
    PRIMARY KEY  (id),
    KEY user_id (user_id),
    KEY action (action),
    KEY created_at (created_at),
    KEY composite_idx (object_type, object_id)
) $charset;";
Query Performance Comparison:
┌────────────────────────────────────┐
│  Without Index: ~2500ms            │
│  ████████████████████████████████  │
│                                    │
│  With Index: ~15ms                 │
│  █                                 │
└────────────────────────────────────┘

Table Versioning

Track your database schema version using WordPress options. Compare versions on plugin load to trigger upgrades when needed—not just on activation.

define('MY_PLUGIN_DB_VERSION', '1.2');

function my_plugin_check_db() {
    $installed_ver = get_option('my_plugin_db_version');
    
    if ($installed_ver !== MY_PLUGIN_DB_VERSION) {
        my_plugin_upgrade_db($installed_ver);
        update_option('my_plugin_db_version', MY_PLUGIN_DB_VERSION);
    }
}
add_action('plugins_loaded', 'my_plugin_check_db');

Upgrade Routines

Handle database migrations by checking the current version and applying incremental updates. Use version comparison to run only necessary upgrades.

function my_plugin_upgrade_db($current_version) {
    global $wpdb;
    
    if (version_compare($current_version, '1.1', '<')) {
        // Upgrade to 1.1: Add new column
        $wpdb->query("
            ALTER TABLE {$wpdb->prefix}my_table 
            ADD COLUMN priority int(11) DEFAULT 0
        ");
    }
    
    if (version_compare($current_version, '1.2', '<')) {
        // Upgrade to 1.2: Add index
        $wpdb->query("
            ALTER TABLE {$wpdb->prefix}my_table 
            ADD INDEX priority_idx (priority)
        ");
    }
    
    // Always run dbDelta for latest schema
    create_my_table();
}

$wpdb->prepare() with Custom Tables

Always use prepare() when incorporating user input into queries—it's WordPress's SQL injection prevention mechanism. Never concatenate variables directly into SQL strings.

global $wpdb;
$table = $wpdb->prefix . 'my_table';

// CORRECT: Using prepare()
$results = $wpdb->get_results(
    $wpdb->prepare(
        "SELECT * FROM $table WHERE user_id = %d AND status = %s",
        $user_id,
        $status
    )
);

// Placeholders: %d (integer), %s (string), %f (float)
// For LIKE queries:
$wpdb->prepare(
    "SELECT * FROM $table WHERE name LIKE %s",
    '%' . $wpdb->esc_like($search) . '%'
);
⚠️  SQL Injection Prevention Flow:
┌──────────────┐     ┌──────────────┐     ┌──────────────┐
│  User Input  │ ──► │  prepare()   │ ──► │  Safe Query  │
│  (untrusted) │     │  (escaping)  │     │  (executed)  │
└──────────────┘     └──────────────┘     └──────────────┘

Query Optimization

Indexing Strategies

Design indexes based on your query patterns: single-column for simple lookups, composite for multi-column WHERE/ORDER BY. Place high-cardinality columns first in composite indexes.

// Analyze your common queries:
// SELECT * FROM wp_orders WHERE user_id = X AND status = Y ORDER BY created_at

// Optimal index for above query:
$sql = "CREATE TABLE {$wpdb->prefix}orders (
    ...
    KEY user_status_date (user_id, status, created_at)
) ...";

// Check existing indexes:
$indexes = $wpdb->get_results("SHOW INDEX FROM {$wpdb->prefix}orders");
Index Selection Guide:
┌─────────────────────────────────────────────────────────┐
│  Query Pattern          →  Recommended Index           │
├─────────────────────────────────────────────────────────┤
│  WHERE a = ?            →  INDEX (a)                   │
│  WHERE a = ? AND b = ?  →  INDEX (a, b)                │
│  WHERE a = ? ORDER BY b →  INDEX (a, b)                │
│  WHERE a = ? AND b > ?  →  INDEX (a, b)                │
└─────────────────────────────────────────────────────────┘

Query Caching

Use WordPress transients or object cache for expensive queries. Cache results with meaningful expiration times and invalidate when data changes.

function get_popular_products() {
    $cache_key = 'popular_products_list';
    $results = get_transient($cache_key);
    
    if ($results === false) {
        global $wpdb;
        $results = $wpdb->get_results("
            SELECT * FROM {$wpdb->prefix}products 
            ORDER BY sales_count DESC LIMIT 10
        ");
        
        set_transient($cache_key, $results, HOUR_IN_SECONDS);
    }
    
    return $results;
}

// Invalidate on data change
function on_product_update($product_id) {
    delete_transient('popular_products_list');
}
Caching Flow:
┌─────────┐    Cache Miss    ┌──────────┐    ┌──────────┐
│ Request │ ───────────────► │ Database │ ──►│  Cache   │
└─────────┘                  └──────────┘    └──────────┘
     │                                            │
     │         Cache Hit (fast!)                  │
     └────────────────────────────────────────────┘

EXPLAIN Usage

Prefix queries with EXPLAIN to analyze execution plans. Look for "Using filesort", "Using temporary", or full table scans indicating optimization opportunities.

global $wpdb;
$explain = $wpdb->get_results("
    EXPLAIN SELECT p.*, pm.meta_value 
    FROM {$wpdb->posts} p
    JOIN {$wpdb->postmeta} pm ON p.ID = pm.post_id
    WHERE pm.meta_key = 'price' AND pm.meta_value > 100
");

print_r($explain);
EXPLAIN Output Analysis:
┌──────┬────────────┬────────┬──────┬─────────┬─────────────────┐
│ id   │ table      │ type   │ rows │ key     │ Extra           │
├──────┼────────────┼────────┼──────┼─────────┼─────────────────┤
│ 1    │ pm         │ ref    │ 150  │ meta_key│ Using where     │ ✓ Good
│ 1    │ p          │ eq_ref │ 1    │ PRIMARY │                 │ ✓ Good
├──────┼────────────┼────────┼──────┼─────────┼─────────────────┤
│ 1    │ logs       │ ALL    │ 50000│ NULL    │ Using filesort  │ ✗ Bad!
└──────┴────────────┴────────┴──────┴─────────┴─────────────────┘

Type ranking (best to worst): system > const > eq_ref > ref > range > index > ALL

Slow Query Identification

Enable MySQL slow query log or use WordPress plugins like Query Monitor. Look for queries exceeding 1 second, especially during page loads.

// Add query logging in development
define('SAVEQUERIES', true);  // In wp-config.php

// Then analyze:
function log_slow_queries() {
    global $wpdb;
    
    foreach ($wpdb->queries as $query) {
        $sql  = $query[0];
        $time = $query[1];  // Execution time
        $caller = $query[2];
        
        if ($time > 0.5) {  // Queries over 500ms
            error_log("SLOW QUERY ({$time}s): $sql | Called by: $caller");
        }
    }
}
add_action('shutdown', 'log_slow_queries');

Query Monitoring

Use Query Monitor plugin for development or implement custom monitoring. Track query count, total time, and identify duplicate queries that should be cached.

// Query Monitor integration for custom queries
do_action('qm/info', 'Custom query executed: ' . $query);

// Manual monitoring class
class DB_Monitor {
    private static $queries = [];
    
    public static function log($query, $time) {
        self::$queries[] = compact('query', 'time');
    }
    
    public static function report() {
        $total = array_sum(array_column(self::$queries, 'time'));
        $count = count(self::$queries);
        return "Queries: $count | Total time: {$total}s";
    }
}
Query Monitor Dashboard:
┌───────────────────────────────────────────────────────────┐
│  Page Load Stats                                          │
├───────────────────────────────────────────────────────────┤
│  Total Queries:     47                                    │
│  Total Time:        0.0823s                               │
│  Duplicate Queries: 3 ⚠️                                  │
├───────────────────────────────────────────────────────────┤
│  Slowest Queries:                                         │
│  ┌─────────────────────────────────────────┬────────────┐ │
│  │ SELECT * FROM wp_postmeta WHERE...     │  0.0234s   │ │
│  │ SELECT * FROM wp_options WHERE...      │  0.0156s   │ │
│  └─────────────────────────────────────────┴────────────┘ │
└───────────────────────────────────────────────────────────┘

Quick Reference Card

┌─────────────────────────────────────────────────────────────────┐
│  WPDB METHOD SELECTION GUIDE                                    │
├─────────────────────────────────────────────────────────────────┤
│                                                                 │
│  Need multiple rows?     → get_results()                        │
│  Need single row?        → get_row()                            │
│  Need single column?     → get_col()                            │
│  Need single value?      → get_var()                            │
│  Insert new data?        → insert()                             │
│  Update existing?        → update()                             │
│  Delete rows?            → delete()                             │
│  Insert or update?       → replace()                            │
│  Custom SQL?             → query() + prepare()                  │
│                                                                 │
├─────────────────────────────────────────────────────────────────┤
│  GOLDEN RULES                                                   │
├─────────────────────────────────────────────────────────────────┤
│  ✓ Always use $wpdb->prepare() with user input                 │
│  ✓ Always use $wpdb->prefix for table names                    │
│  ✓ Check $wpdb->last_error after operations                    │
│  ✓ Use transients for expensive queries                        │
│  ✓ Add indexes for frequently queried columns                  │
│  ✗ Never trust user input in SQL                               │
│  ✗ Never hardcode table prefixes                               │
└─────────────────────────────────────────────────────────────────┘