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

[swissspidy]

This was just discussed in a bug scrub.

Loading that many posts at once is not really a recommended practice, but of course can be done with the right precautions.

As per the linked support topic, the query hook can be used to modify queries if needed.

Closing as wontfix though as I don't see core doing anything here in this regard, and the "patch" above seems like a project-specific hack/workaround.