MySQL views explained with WordPress database examples

A MySQL view is a stored SELECT query that you can treat as a virtual table — you give it a name and then query it with SELECT * FROM view_name just like a regular table. Views do not store data themselves; they re-execute the underlying query every time you select from them. Their main benefits are: encapsulating complex joins and subqueries into a reusable, named object; simplifying application code (your PHP can do SELECT * FROM post_stats instead of a 10-line JOIN); providing a read-only abstraction layer (you can grant a database user SELECT on a view without exposing the underlying tables); and creating stable column aliases when your raw table column names change. In WordPress, views are particularly useful for analytics dashboards (post stats, user activity), admin reports, and any situation where the same complex multi-table query appears in multiple places. The CREATE OR REPLACE VIEW syntax lets you update a view definition without dropping and recreating it. Views that reference functions like NOW() or COUNT() are not updateable, but for read-only reporting purposes that is irrelevant. Note that WordPress’s $wpdb can query views exactly like tables — just use the view name where you would use a table name. Combine views with the techniques in the GROUP BY guide and the EXPLAIN guide for a complete MySQL reporting toolkit.

Problem: You have complex multi-table WordPress database queries that are repeated across your codebase and you want to centralise them into reusable, named database objects.

Solution: Create the following views in phpMyAdmin or via $wpdb on plugin activation:

-- View 1: Published post stats per category
CREATE OR REPLACE VIEW vw_post_stats AS
SELECT
    t.name                          AS category_name,
    t.slug                          AS category_slug,
    tt.term_taxonomy_id,
    COUNT(tr.object_id)             AS post_count,
    MAX(p.post_date)                AS latest_post_date
FROM ynYYAa7bFT_terms t
JOIN ynYYAa7bFT_term_taxonomy tt
    ON tt.term_id = t.term_id AND tt.taxonomy = 'category'
LEFT JOIN ynYYAa7bFT_term_relationships tr
    ON tr.term_taxonomy_id = tt.term_taxonomy_id
LEFT JOIN ynYYAa7bFT_posts p
    ON p.ID = tr.object_id AND p.post_status = 'publish' AND p.post_type = 'post'
GROUP BY t.term_id, t.name, t.slug, tt.term_taxonomy_id;

-- Query the view like a table
SELECT * FROM vw_post_stats ORDER BY post_count DESC;

-- View 2: Posts with their primary category and tag list
CREATE OR REPLACE VIEW vw_posts_with_terms AS
SELECT
    p.ID,
    p.post_title,
    p.post_date,
    p.post_name                                                          AS slug,
    MAX(CASE WHEN tt.taxonomy = 'category' THEN t.name END)             AS primary_category,
    GROUP_CONCAT(CASE WHEN tt.taxonomy = 'post_tag' THEN t.name END
                 ORDER BY t.name SEPARATOR ', ')                         AS tags
FROM ynYYAa7bFT_posts p
LEFT JOIN ynYYAa7bFT_term_relationships tr ON tr.object_id = p.ID
LEFT JOIN ynYYAa7bFT_term_taxonomy tt     ON tt.term_taxonomy_id = tr.term_taxonomy_id
LEFT JOIN ynYYAa7bFT_terms t              ON t.term_id = tt.term_id
WHERE p.post_status = 'publish' AND p.post_type = 'post'
GROUP BY p.ID, p.post_title, p.post_date, p.post_name;

-- Drop a view
-- DROP VIEW IF EXISTS vw_post_stats;

// Query a view via $wpdb in WordPress
function helloadmin_get_category_stats(): array {
    global $wpdb;
    return $wpdb->get_results(
        "SELECT category_name, post_count, latest_post_date
         FROM vw_post_stats
         ORDER BY post_count DESC
         LIMIT 10",
        ARRAY_A
    );
}

// Create view on plugin activation
register_activation_hook( __FILE__, 'helloadmin_create_views' );
function helloadmin_create_views(): void {
    global $wpdb;
    $pfx = $wpdb->prefix;
    $wpdb->query( "
        CREATE OR REPLACE VIEW {$pfx}vw_post_stats AS
        SELECT t.name AS category_name, COUNT(tr.object_id) AS post_count
        FROM {$pfx}terms t
        JOIN {$pfx}term_taxonomy tt ON tt.term_id = t.term_id AND tt.taxonomy = 'category'
        LEFT JOIN {$pfx}term_relationships tr ON tr.term_taxonomy_id = tt.term_taxonomy_id
        LEFT JOIN {$pfx}posts p ON p.ID = tr.object_id AND p.post_status = 'publish'
        GROUP BY t.term_id, t.name
    " );
}

NOTE: Views in MySQL are not materialised — they re-run the underlying SELECT every time you query them. On large WordPress databases (100k+ posts), a complex view with multiple joins can be slower than the equivalent indexed direct query. For performance-critical reporting, consider creating a summary table that is refreshed periodically with WP-Cron instead of relying on a view. Also note that wpdb’s table prefix replacement (using $wpdb->prefix) does not automatically apply to view names — you need to include the prefix explicitly when creating views from PHP.