[wp-trac] [WordPress Trac] #40404: Slow sampling when a large number of posts from a big 10-ing thousand
WordPress Trac
noreply at wordpress.org
Mon Apr 10 19:23:05 UTC 2017
#40404: Slow sampling when a large number of posts from a big 10-ing thousand
-------------------------+-----------------------------
Reporter: xrayboy | Owner:
Type: enhancement | Status: new
Priority: normal | Milestone: Awaiting Review
Component: General | Version: 4.7.3
Severity: normal | Keywords:
Focuses: performance |
-------------------------+-----------------------------
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>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform
More information about the wp-trac
mailing list