Fix SQL_CALC_FOUND_ROWS performance degradation on WordPress, fix database indexes and primary keys.

pantheon-sql-perf.php

<?php

/**
 * Plugin Name: WordPress SQL Performance
 * Plugin URI: https://www.pantheon.io
 * Description: Fix SQL_CALC_FOUND_ROWS performance degradation.
 * Author: Kyle Taylor, Pantheon
 * Version: 1.0
 * License: GPL2
 * Credit: https://wpartisan.me/tutorials/wordpress-database-queries-speed-sql_calc_found_rows
 */

if (!function_exists('pantheon_sql_perf_set_no_found_rows')) :

    /**
     * Sets the 'no_found_rows' param to true.
     *
     * In the WP_Query class this stops the use of SQL_CALC_FOUND_ROWS in the
     * MySql query it generates. It's slow so we're going to replace it with
     * a COUNT(*) instead.
     *
     * @param  WP_Query $wp_query The WP_Query instance. Passed by reference.
     * @return void
     */
    function pantheon_sql_perf_set_no_found_rows(\WP_Query $wp_query)
    {
        $wp_query->set('no_found_rows', true);
    }
endif;
add_filter('pre_get_posts', 'pantheon_sql_perf_set_no_found_rows', 10, 1);

if (!function_exists('pantheon_sql_perf_set_found_posts')) :

    /**
     * Workout the pagination values.
     *
     * Uses the query parts to run a custom count(*) query against the database
     * then constructs and sets the pagination results for this wp_query.
     *
     * @param array    $clauses  Array of clauses that make up the SQL query.
     * @param WP_Query $wp_query The WP_Query instance. Passed by reference.
     * @return array
     */
    function pantheon_sql_perf_set_found_posts($clauses, \WP_Query $wp_query)
    {

        // Don't proceed if it's a singular page.
        if ($wp_query->is_singular()) {
            return $clauses;
        }

        global $wpdb;

        // Check if they're set.
        $where = isset($clauses['where']) ? $clauses['where'] : '';
        $join = isset($clauses['join']) ? $clauses['join'] : '';
        $distinct = isset($clauses['distinct']) ? $clauses['distinct'] : '';

        // Construct and run the query. Set the result as the 'found_posts'
        // param on the main query we want to run.
        $wp_query->found_posts = $wpdb->get_var("SELECT $distinct COUNT(*) FROM {$wpdb->posts} $join WHERE 1=1 $where");

        // Work out how many posts per page there should be.
        $posts_per_page = (!empty($wp_query->query_vars['posts_per_page']) ? absint($wp_query->query_vars['posts_per_page']) : absint(get_option('posts_per_page')));

        // Set the max_num_pages.
        $wp_query->max_num_pages = ceil($wp_query->found_posts / $posts_per_page);

        // Return the $clauses so the main query can run.
        return $clauses;
    }
endif;
add_filter('posts_clauses', 'pantheon_sql_perf_set_found_posts', 10, 2);

wp-db-fix-queries.sql

-- Reset WordPress Core Database Keys and Indexes
-- https://codex.wordpress.org/Database_Description#Table_Details
-- WARNING: Not meant to run as single SQL statement. Chunk out as necessary.

-- Fix WordPress table keys
ALTER TABLE `wp_links` MODIFY link_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY
ALTER TABLE `wp_users` MODIFY ID BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY
ALTER TABLE `wp_usermeta` MODIFY umeta_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY
ALTER TABLE `wp_terms` MODIFY term_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY
ALTER TABLE `wp_termmeta` MODIFY meta_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY
ALTER TABLE `wp_term_taxonomy` ADD UNIQUE (term_id)
ALTER TABLE `wp_term_taxonomy` MODIFY term_taxonomy_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY
ALTER TABLE `wp_postmeta` MODIFY meta_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY
ALTER TABLE `wp_posts` MODIFY ID BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY
ALTER TABLE `wp_options` ADD UNIQUE (option_name)
ALTER TABLE `wp_options` MODIFY option_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY

-- Fix maximum index size
ALTER TABLE `wp_postmeta` DROP INDEX meta_key, ADD INDEX meta_key(meta_key(191));

-- Fix wp_postmeta index
CREATE PRIMARY INDEX meta_id_idx ON wp_postmeta(meta_id)
CREATE INDEX post_id_idx ON wp_postmeta(post_id)
CREATE INDEX meta_key_idx ON wp_postmeta(meta_key)

-- Fix wp_posts index
CREATE PRIMARY INDEX post_id_idx ON wp_posts(ID)
CREATE INDEX post_name_idx ON wp_posts(post_name)
CREATE INDEX type_status_date_idx ON wp_posts(post_type, post_status, post_date, ID)
CREATE INDEX post_parent_idx ON wp_posts(post_parent)
CREATE INDEX post_author_idx ON wp_posts(post_author)

-- Fix wp_terms index
CREATE PRIMARY INDEX term_id_idx ON wp_terms(term_id)
CREATE INDEX term_slid_idx ON wp_terms(slug(2))

-- Fix wp_term_relationships index
CREATE PRIMARY INDEX primary_id ON wp_term_relationships(object_id, term_taxonomy_id)
CREATE INDEX term_taxonomy_id_idx ON wp_term_relationships(term_taxonomy_id)

-- Fix wp_term_taxonomy index
CREATE PRIMARY INDEX term_taxonomy_id_idx ON wp_term_taxonomy(term_taxonomy_id)
CREATE UNIQUE INDEX term_id_taxonomy_idx ON wp_term_taxonomy(term_id, taxonomy)
CREATE INDEX taxonomy_idx ON wp_term_taxonomy(taxonomy)