Large AND OR query timing out

时间:2017-02-04 作者:James

我正在开发wordpress日历插件,遇到了自定义字段的问题。我可以执行两个单独的查询,一个用于重复事件,另一个用于静态事件,但当我尝试将它们组合在一起时,我的脚本进程将一直持续到服务器崩溃并重新启动mysql。。。

$args = array(
    \'numberposts\'   => -1,
    \'post_type\'     => \'events\',
    \'meta_query\'    => array(
        \'relation\' => \'OR\',
        array(
            \'relation\' => \'AND\',
            array( \'key\' => \'calendar\', \'value\' => $cid, \'compare\' => \'=\'),
            array( \'key\' => \'event_date\', \'value\' => $startQuery, \'compare\' => \'>=\' ),
            array( \'key\' => \'event_date\', \'value\' => $endQuery, \'compare\' => \'<=\' ),
            array( \'key\' => \'does_this_event_repeat\', \'value\' => \'1\', \'compare\' => \'=\'),
            array( \'key\' => \'event_category\', \'value\' => \'"\'.$category.\'"\',
           \'compare\' => \'LIKE\'),
        ),
        array(
            \'relation\' => \'AND\',
            array( \'key\' => \'calendar\', \'value\' => $cid, \'compare\' => \'=\'),
            array( \'key\' => \'event_date\', \'value\' => $endQuery, \'compare\' => \'<=\' ),
            array( \'key\' => \'repeat_until\', \'value\' => $startQuery, \'compare\' => \'>=\' ),
            array( \'key\' => \'does_this_event_repeat\', \'value\' => \'1\', \'compare\' => \'>\'),
            array( \'key\' => \'event_category\', \'value\' => \'"\'.$category.\'"\',
           \'compare\' => \'LIKE\')
        ),
    )
);
$the_query = new WP_Query( $args );
更新时间:

请帮助我找出为什么顶部不起作用,但下面的代码起作用。我将精确的代码分解为两个查询,然后将它们合并为一个查询。

$static = array(
    \'numberposts\'   => -1,
    \'post_type\'     => \'protean_event\',
    \'meta_query\'    => array(
        \'relation\' => \'AND\',
        array( \'key\' => \'calendar\', \'value\' => $cid, \'compare\' => \'=\'),
        array( \'key\' => \'event_date\', \'value\' => $startQuery, \'compare\' => \'>=\' ),
        array( \'key\' => \'event_date\', \'value\' => $endQuery, \'compare\' => \'<=\' ),
        array( \'key\' => \'does_this_event_repeat\', \'value\' => \'1\', \'compare\' => \'=\'),
        array( \'key\' => \'event_category\', \'value\' => $category, \'compare\' => \'LIKE\'),
    )
);
$repeating = array(
    \'numberposts\'   => -1,
    \'post_type\'     => \'protean_event\',
    \'meta_query\'    => array(
        \'relation\' => \'AND\',
        array( \'key\' => \'calendar\', \'value\' => $cid, \'compare\' => \'=\'),
        array( \'key\' => \'event_date\', \'value\' => $endQuery, \'compare\' => \'<=\' ),
        array( \'key\' => \'repeat_until\', \'value\' => $startQuery, \'compare\' => \'>=\' ),
        array( \'key\' => \'does_this_event_repeat\', \'value\' => \'1\', \'compare\' => \'>\'),
        array( \'key\' => \'event_category\', \'value\' => $category, \'compare\' => \'LIKE\'),
    )
);
$query1 = new WP_Query($static);
$query2 = new WP_Query($repeating);

//create new empty query and populate it with the other two
$wp_query = new WP_Query();
$wp_query->posts = array_merge( $query1->posts, $query2->posts );

//populate post_count count for the loop to work correctly
$wp_query->post_count = $query1->post_count + $query2->post_count;

2 个回复
SO网友:Mark Kaplun

首先,任何涉及LIKE 只是不能太快。如果想要快速查询,请避免LIKE.

最重要的是,基本上您的组合查询并不关心是否有重复,因此只需一个变体而不检查重复字段就足够了。

现在很难说服务器崩溃的原因,但大多数情况下,这是内存不足的症状。这里的问题是,是mySQL耗尽了内存,还是PHP端耗尽了内存。在任何情况下,当您有许多结果时,请求一个未绑定数量的结果都会对您造成影响。

SO网友:James

问题在于最初的或者我改造了庄园,它在庄园中收集数据,并在瞬间发挥作用。

$args = array(
    \'numberposts\'   => -1,
    \'post_type\'     => \'protean_event\',
    \'meta_query\'    => array(
        \'relation\' => \'AND\',
        array( \'key\' => \'calendar\', \'value\' => $cid, \'compare\' => \'=\'),
        array(
            \'relation\' => \'OR\',
            array(
                \'relation\' => \'AND\',
                array( \'key\' => \'event_date\', \'value\' => $startQuery, \'compare\' => \'>=\' ),
                array( \'key\' => \'event_date\', \'value\' => $endQuery, \'compare\' => \'<=\' ),
                array( \'key\' => \'does_this_event_repeat\', \'value\' => \'1\', \'compare\' => \'=\'),
            ),
            array(
                \'relation\' => \'AND\',
                array( \'key\' => \'event_date\', \'value\' => $endQuery, \'compare\' => \'<=\' ),
                array( \'key\' => \'repeat_until\', \'value\' => $startQuery, \'compare\' => \'>=\' ),
                array( \'key\' => \'does_this_event_repeat\', \'value\' => \'1\', \'compare\' => \'>\'),
            )
        ),
        array( \'key\' => \'event_category\', \'value\' => \'"\'.$category.\'"\', \'compare\' => \'LIKE\')
    )
);