Slow queries with a large number of posts (tens of thousands and above)

[xrayboy]

translation yandex:
TASK:
Faced with such a problem that when the number of posts of 20 000+, the sample was held for 30 seconds each page. That is, the database constantly selects all records to select all of a certain number (items per page). The computer is weak for the global scale, but plans to increase to 7 000 000+ posts. Usually hard disks are not the problem.
QUESTION: is There a possibility to optimize the sample?
My SOLUTION (working in project MySQL): (maybe I should add this decision to the release of WP)
Therefore, the solution is selected such that, upon receipt of a sample using the function get_posts () (wp-includes/class-wp-query.php) you need to "intercept" a standard request, modify the request function and give the already constructed list.
To store "lists" of sorting in the database was created table:

###
CREATE TABLE wp_сорт (

номер_сорта int(10) unsigned NOT NULL AUTO_INCREMENT,
значение_сорта text NOT NULL,
время_сорта datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
период_сорта int(10) unsigned NOT NULL,
мд5_сорта varchar(32) NOT NULL,
записей_сорта int(10) unsigned NOT NULL,
вызов_сорта datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
PRIMARY KEY (номер_сорта),
UNIQUE KEY инд_мд5_сорта (мд5_сорта) USING BTREE

) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8mb4;

CREATE TABLE wp_сортировка (

номер_поста bigint(20) unsigned NOT NULL,
номер_страница int(10) unsigned NOT NULL DEFAULT '0',
номер_на_странице tinyint(3) unsigned NOT NULL DEFAULT '0',
номер_сорта int(10) unsigned NOT NULL DEFAULT '1',
UNIQUE KEY уник (номер_страница,номер_на_странице,номер_сорта) USING BTREE,
KEY внешка_поста (номер_поста) USING BTREE,
KEY внешка_сорта (номер_сорта),
CONSTRAINT внешка_поста FOREIGN KEY (номер_поста) REFERENCES wp_posts (ID) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT внешка_сорта FOREIGN KEY (номер_сорта) REFERENCES wp_сорт (номер_сорта) ON DELETE CASCADE ON UPDATE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
###

Modification of the file wp-includes/class-wp-query.php in the function get_posts () somewhere 2513 string to find:

###

<?php
if ( $split_the_query ) {
    // First get the IDs and then fill in the objects

###

and insert the following:

###

<?php
$bMod = isset( $q[ "posts_per_page" ] ) && $q[ "posts_per_page" ] > 0 && !isset( $q[ 'search_terms' ] );
if ( $bMod ) {
        $мЗначение = [
                "join"           => $join,
                "where"          => $where,
                "groupby"        => $groupby,
                "orderby"        => $orderby,
                "posts_per_page" => $q[ "posts_per_page" ]
        ];
        $сЗначение = serialize ( $мЗначение );
        $сМд5 = md5 ( $сЗначение );
        $aR = $wpdb->get_results ( "SELECT `номер_сорта`, `записей_сорта` FROM `wp_сорт` 
        WHERE 
        `мд5_сорта`='{$сМд5}'", ARRAY_A );
        $sTime = date ( "Y-m-d H:i:s", strtotime ( "now" ) + 14400 );
        if ( !$aR ) {
                $stable = "wp_сорт";
                $sColumName = "номер_сорта";
                $iN = $wpdb->get_var ( 'SELECT t1.' . $sColumName . '+1
                  FROM ' . $stable . ' AS t1
                  LEFT JOIN ' . $stable . ' AS t2
                  ON t1.' . $sColumName . '+1 = t2.' . $sColumName . '
                  WHERE t2.' . $sColumName . ' IS NULL
                  ORDER BY t1.' . $sColumName . '
                  LIMIT 1' );
                $wpdb->insert (
                        "wp_сорт",
                        [
                                "номер_сорта"    => $iN,
                                "значение_сорта" => $сЗначение,
                                "период_сорта"   => 86400,
                                "мд5_сорта"      => $сМд5,
                                "вызов_сорта"    => $sTime
                        ],
                        [
                                '%d',
                                '%s',
                                '%d',
                                '%s',
                                '%s'
                        ]
                );
                $чНомерСорта = $wpdb->insert_id;
                $iPerPage = $q[ "posts_per_page" ] - 1;
                $iQ = $wpdb->query (
                        "SET @iVar = -1;" );
                $iQ = $wpdb->query (
                        "SET @iVar2 = 0;" );
                $sQ = "INSERT INTO `wp_сортировка`
                SELECT SQL_CALC_FOUND_ROWS @iVar3:=wp_posts.ID,
                        IF(@iVar={$iPerPage},@iVar2:=@iVar2+1,IF(ISNULL(@iVar2), @iVar2:=0 , @iVar2)) t,
                        IF(@iVar<" . $iPerPage . ",@iVar:=@iVar+1,@iVar:=0) t1,
                        {$чНомерСорта}
                FROM wp_posts {$join}
                WHERE 1=1 {$where} {$groupby} {$orderby}
                ON DUPLICATE KEY UPDATE `номер_поста`=@iVar3;";
                $wpdb->query ( $sQ );
                $q[ "iRows" ] = $iRows = $wpdb->get_var ( "SELECT FOUND_ROWS()" );
                $wpdb->update ( "wp_сорт", [
                        "записей_сорта" => $iRows, "время_сорта" => $sTime
                ],
                        [ "номер_сорта" => $чНомерСорта ] );
        } else {
                $чНомерСорта = $aR[ 0 ][ "номер_сорта" ];
                $wpdb->update ( "wp_сорт", [ "вызов_сорта" => $sTime ],
                        [ "номер_сорта" => $чНомерСорта ] );
                $q[ "iRows" ] = $iRows = $aR [ 0 ][ "записей_сорта" ];
        }
        if ( isset ( $q[ "paged" ] ) && $q[ "paged" ] )
                $iPaged = $q[ "paged" ] - 1;
        else
                $iPaged = 0;
        $this->request = "SELECT $found_rows $distinct {$wpdb->posts}.ID FROM 
        {$wpdb->posts} $join WHERE 
{$wpdb->posts}.ID IN(SELECT `номер_поста` FROM `wp_сортировка` WHERE `номер_страница`={$iPaged} 
AND `номер_сорта`={$чНомерСорта}) GROUP BY {$wpdb->posts}.ID";
        //echo 0;
} else {
        $this->request = "SELECT $found_rows $distinct {$wpdb->posts}.ID FROM {$wpdb->posts} $join WHERE 
        1=1 $where $groupby $orderby $limits";
}

###

To give the number of records in set_found_posts function ( $q, $limits ) change:

###

<?php
$this->found_posts = $wpdb->get_var ( apply_filters_ref_array ( 'found_posts_query', [ 'SELECT FOUND_ROWS()', &$this ] ) );

###

on:

###

<?php
if ( isset( $q[ "iRows" ] ) )
        $this->found_posts = $q[ "iRows" ];
else
        $this->found_posts = $wpdb->get_var ( apply_filters_ref_array ( 'found_posts_query', [ 'SELECT FOUND_ROWS()', &$this ] ) );

###

in cron you need to add one-time during installation, for example, the plugin task:

###

<?php
wp_schedule_event ( time (), 'wp_wc_updater_cron_interval', 'xray_sorting_event' );

###

at the event:

###

<?php
add_action ( 'xray_sorting_event', 'xray_sorting_event_func' );

###

and, accordingly, the function itself:

###

<?php
/**
 * Функция для cron событий
 */
function xray_sorting_event_func () {
    global $wpdb;
    set_time_limit ( 600 );
    echo '<pre>xray_sorting';
    $aR = $wpdb->get_results ( "SELECT * FROM `wp_сорт` WHERE (NOW()>DATE_ADD(`время_сорта`,INTERVAL `период_сорта` 
    SECOND) OR `время_сорта`<`вызов_сорта`) AND `номер_сорта`>0", ARRAY_A );
    foreach ( $aR as $aV ) {
        $aЗначение = unserialize ( $aV[ "значение_сорта" ] );
        $iPerPage = $aЗначение[ "posts_per_page" ] - 1;
        $iQ = $wpdb->query (
            "SET @iVar = -1;" );
        $iQ = $wpdb->query (
            "SET @iVar2 = 0;" );
        $sQ = "INSERT LOW_PRIORITY INTO `wp_сортировка`
        SELECT SQL_CALC_FOUND_ROWS @iVar3:=wp_posts.ID,
            IF(@iVar={$iPerPage},@iVar2:=@iVar2+1,IF(ISNULL(@iVar2), @iVar2:=0 , @iVar2)) t,
            IF(@iVar<" . $iPerPage . ",@iVar:=@iVar+1,@iVar:=0) t1,
            {$aV["номер_сорта"]}
        FROM wp_posts {$aЗначение["join"]}
        WHERE 1=1 {$aЗначение["where"]} {$aЗначение["groupby"]} {$aЗначение["orderby"]}
        ON DUPLICATE KEY UPDATE `номер_поста`=@iVar3;";
        $iQ = $wpdb->query ( $sQ );
        if ( !$wpdb->last_error == "" ) {
            echo "\nсорт {$iQ} " . $aV[ "номер_сорта" ] . ": провал: " . htmlspecialchars ( $sQ ) . " " . $wpdb->last_error
                . ";";
        } else {
            echo "\nсорт {$iQ} " . $aV[ "номер_сорта" ] . ": норма;";
        }
        $iRows = $wpdb->get_var ( "SELECT FOUND_ROWS()" );
        echo $wpdb->update ( "wp_сорт", [
            "записей_сорта" => $iRows, "время_сорта" => date ( "Y-m-d H:i:s", strtotime
                ( "now" ) + 14400 )
                        ],
            [
                "номер_сорта" => $aV[ "номер_сорта" ]
            ] );
    }
    # Стираем старые записи сортировок 604800 сек - это 7 суток
    $wpdb->query ( "DELETE FROM `wp_сорт` WHERE `вызов_сорта`<" . date ( "Y-m-d H:i:s", strtotime ( "now" ) - 604800 ) . "  AND `номер_сорта`>0" );
}

###

I repeat that this is a working model. Can requires file. But all the errors which may be in code(commas, spaces, something like that) is the problem of copy-paste.
​https://ru.wordpress.org/support/topic/%d0%bc%d0%b5%d0%b4%d0%bb%d0%b5%d0%bd%d0%bd%d0%b0%d1%8f-%d1%80%d0%b0%d0%b1%d0%be%d1%82%d1%8b-%d0%b2%d1%8b%d0%b1%d0%be%d1%80%d0%ba%d0%b8-%d0%bf%d1%80%d0%b8-%d0%b1%d0%be%d0%bb%d1%8c%d1%88%d0%be%d0%bc/

[xrayboy]

file source