wordpress - Wordpress DB 经常被锁定

Question

由于以下由 woocommerce 订阅自动触发的查询，Wordpress 被锁定

| Query   |  630 | Creating sort index | SELECT searchdate.Date as date, COUNT( DISTINCT wcsubs.ID) as count
                            FROM (
                                    SELECT DATE_FORMAT(a.Date,'%Y-%m-%d') as Date, 0 as cnt
                                    FROM (
                                            SELECT DATE('2018-04-25') - INTERVAL(a.a + (10 * b.a) + (100 * c.a)) DAY as Date
                                            FROM (
                                                    SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2
                                                    UNION ALL SELECT 3 UNION ALL SELECT 4
                                                    UNION ALL SELECT 5 UNION ALL SELECT 6
                                                    UNION ALL SELECT 7 UNION ALL SELECT 8
                                                    UNION ALL SELECT 9
                                            ) as a
                                            CROSS JOIN (
                                                    SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2
                                                    UNION ALL SELECT 3 UNION ALL SELECT 4
                                                    UNION ALL SELECT 5 UNION ALL SELECT 6
                                                    UNION ALL SELECT 7 UNION ALL SELECT 8
                                                    UNION ALL SELECT 9
                                            ) as b
                                            CROSS JOIN (
                                                    SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2
                                                    UNION ALL SELECT 3 UNION ALL SELECT 4
                                                    UNION ALL SELECT 5 UNION ALL SELECT 6
                                                    UNION ALL SELECT 7 UNION ALL SELECT 8
                                                    UNION ALL SELECT 9
                                            ) AS c
                                    ) a
                                    WHERE a.Date >= '2018-01-01' AND a.Date <= '2018-04-25'
                            ) searchdate
                            LEFT JOIN (
                                    wp_tvhh73tyf8_posts AS wcsubs
                                    LEFT JOIN wp_tvhh73tyf8_postmeta AS wcsmeta
                                            ON wcsubs.ID = wcsmeta.post_id AND wcsmeta.meta_key = '_schedule_end'
                            ) ON DATE( wcsubs.post_date ) <= searchdate.Date
                                    AND wcsubs.post_type IN ( 'shop_subscription' )
                                    AND wcsubs.post_status NOT IN( 'wc-pending', 'trash' )
                                    AND (
                                            DATE( wcsmeta.meta_value ) >= searchdate.Date
                                            OR wcsmeta.meta_value = 0
                                            OR wcsmeta.meta_value IS NULL
                                    )
                            GROUP BY searchdate.Date
                            ORDER BY searchdate.Date ASC |

我的网站在 4core 中运行，内存为 16gb

标签： wordpresswoocommercewoocommerce-subscriptions

Answer 1

我通过编辑查询来解决，WHERE而不是AND在JOINs 子句中使用。

结果是相同的，并且与原始查询相比要快一点。

查询位于此路径中：

<WP ROOT>/wp-content/plugins/woocommerce-subscriptions/includes/admin/reports/class-wcs-report-subscription-events-by-date.php

第 225 行（在 WooCommerce v3.4.4 中）。

    /*
     * Subscribers by date
     */
    $query = $wpdb->prepare(
        "SELECT searchdate.Date as date, COUNT( DISTINCT wcsubs.ID) as count
            FROM (
                SELECT DATE_FORMAT(a.Date,'%%Y-%%m-%%d') as Date, 0 as cnt
                FROM (
                    SELECT DATE(%s) - INTERVAL(a.a + (10 * b.a) + (100 * c.a)) DAY as Date
                    FROM (
                        SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2
                        UNION ALL SELECT 3 UNION ALL SELECT 4
                        UNION ALL SELECT 5 UNION ALL SELECT 6
                        UNION ALL SELECT 7 UNION ALL SELECT 8
                        UNION ALL SELECT 9
                    ) as a
                    CROSS JOIN (
                        SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2
                        UNION ALL SELECT 3 UNION ALL SELECT 4
                        UNION ALL SELECT 5 UNION ALL SELECT 6
                        UNION ALL SELECT 7 UNION ALL SELECT 8
                        UNION ALL SELECT 9
                    ) as b
                    CROSS JOIN (
                        SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2
                        UNION ALL SELECT 3 UNION ALL SELECT 4
                        UNION ALL SELECT 5 UNION ALL SELECT 6
                        UNION ALL SELECT 7 UNION ALL SELECT 8
                        UNION ALL SELECT 9
                    ) AS c
                ) a
                WHERE a.Date >= %s AND a.Date <= %s
            ) searchdate
            LEFT JOIN (
                SELECT 
                    wcsubs.ID,
                    wcsubs.post_type,
                    wcsubs.post_status,
                    wcsubs.post_date,
                    wcsmeta.meta_value
                FROM {$wpdb->posts} AS wcsubs
                LEFT JOIN {$wpdb->postmeta} AS wcsmeta
                    ON wcsubs.ID = wcsmeta.post_id
                WHERE
                    wcsmeta.meta_key = %s
            ) AS wcsubs ON DATE( wcsubs.post_date ) <= searchdate.Date
            WHERE 
                wcsubs.post_type IN ( 'shop_subscription' )
                AND wcsubs.post_status NOT IN( 'wc-pending', 'trash', 'auto-draft' )
                AND (
                    DATE( wcsubs.meta_value ) >= searchdate.Date
                    OR wcsubs.meta_value = 0
                    OR wcsubs.meta_value IS NULL
                )
            GROUP BY searchdate.Date
            ORDER BY searchdate.Date ASC",
        $query_end_date,
        date('Y-m-d', $this->start_date),
        $query_end_date,
        wcs_get_date_meta_key('end')
    );