[wp-trac] [WordPress Trac] #40404: Slow queries with a large number of posts (tens of thousands and above)
WordPress Trac
noreply at wordpress.org
Wed Feb 12 16:14:17 UTC 2025
#40404: Slow queries with a large number of posts (tens of thousands and above)
-------------------------+------------------------------
Reporter: xrayboy | Owner: (none)
Type: enhancement | Status: new
Priority: normal | Milestone: Awaiting Review
Component: Query | Version: 4.7.3
Severity: normal | Resolution:
Keywords: | Focuses: performance
-------------------------+------------------------------
Description changed by swissspidy:
Old description:
> 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
> <?php
> if ( $split_the_query ) {
> // First get the IDs and then fill in the objects
> }}}
> ###
>
> and insert the following:
>
> ###
> {{{#!php
> <?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}, at iVar2:=@iVar2+1,IF(ISNULL(@iVar2), @iVar2:=0 ,
> @iVar2)) t,
> IF(@iVar<" . $iPerPage .
> ", at iVar:=@iVar+1, at 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
> <?php
> $this->found_posts = $wpdb->get_var ( apply_filters_ref_array (
> 'found_posts_query', [ 'SELECT FOUND_ROWS()', &$this ] ) );
> }}}
> ###
>
> on:
>
> ###
> {{{#!php
> <?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
> <?php
> wp_schedule_event ( time (), 'wp_wc_updater_cron_interval',
> 'xray_sorting_event' );
> }}}
> ###
>
> at the event:
>
> ###
> {{{#!php
> <?php
> add_action ( 'xray_sorting_event', 'xray_sorting_event_func' );
> }}}
> ###
>
> and, accordingly, the function itself:
>
> ###
> {{{#!php
> <?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}, at iVar2:=@iVar2+1,IF(ISNULL(@iVar2),
> @iVar2:=0 , @iVar2)) t,
> IF(@iVar<" . $iPerPage . ", at iVar:=@iVar+1, at 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/]
New description:
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
<?php
if ( $split_the_query ) {
// First get the IDs and then fill in the objects
}}}
and insert the following:
{{{#!php
<?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}, at iVar2:=@iVar2+1,IF(ISNULL(@iVar2), @iVar2:=0 ,
@iVar2)) t,
IF(@iVar<" . $iPerPage .
", at iVar:=@iVar+1, at 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
<?php
$this->found_posts = $wpdb->get_var ( apply_filters_ref_array (
'found_posts_query', [ 'SELECT FOUND_ROWS()', &$this ] ) );
}}}
on:
{{{#!php
<?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
<?php
wp_schedule_event ( time (), 'wp_wc_updater_cron_interval',
'xray_sorting_event' );
}}}
at the event:
{{{#!php
<?php
add_action ( 'xray_sorting_event', 'xray_sorting_event_func' );
}}}
and, accordingly, the function itself:
{{{#!php
<?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}, at iVar2:=@iVar2+1,IF(ISNULL(@iVar2),
@iVar2:=0 , @iVar2)) t,
IF(@iVar<" . $iPerPage . ", at iVar:=@iVar+1, at 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/]
--
--
Ticket URL: <https://core.trac.wordpress.org/ticket/40404#comment:4>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform
More information about the wp-trac
mailing list