mysql - 优化sql查询-wordpress-sql_big_selects

Question

我有一个很大的 SQL 查询，需要以某种方式简化它，感谢任何帮助。

我收到此错误:WordPress 数据库错误:[SELECT 将检查超过 MAX_JOIN_SIZE 行；检查您的 WHERE 并使用 SET SQL_BIG_SELECTS=1 或 SET MAX_JOIN_SIZE=#(如果 SELECT 没问题)]

我设置了 $wpdb->query('SET SQL_BIG_SELECTS = 1') 并且站点每次都会永远加载。

不想使用“SET SQL_BIG_SELECTS = 1”或wordpress meta_query，只是为了简化此查询。

我提出了一个新问题，因为我没有适当的 sql 技能。

我现在使用的查询:

        $querystr = "SELECT $wpdb->posts.ID
            FROM
               $wpdb->posts,
               $wpdb->postmeta AS app_date,        
               $wpdb->postmeta AS provider,
               $wpdb->postmeta AS time1,
               $wpdb->postmeta AS time2,
               $wpdb->postmeta AS client

            WHERE
               $wpdb->posts.ID = app_date.post_id          
            AND
               $wpdb->posts.ID = provider.post_id
            AND
               $wpdb->posts.ID = time1.post_id
            AND
               $wpdb->posts.ID = time2.post_id
            AND
               $wpdb->posts.ID = client.post_id

            AND $wpdb->posts.post_type = 'ga_appointments'
            AND $wpdb->posts.post_status IN ('completed', 'publish', 'payment', 'pending')                 

            AND app_date.meta_key   = 'ga_appointment_date'
            AND app_date.meta_value = 2018-03-27

            AND provider.meta_key   = 'ga_appointment_provider'
            AND provider.meta_value = 55

            AND time1.meta_key = 'ga_appointment_time_end'
            AND time1.meta_value > 13:05

            AND time2.meta_key = 'ga_appointment_time'
            AND time2.meta_value < 14:05

            OR  client.meta_key = 'ga_appointment_client'
            AND client.meta_value REGEXP '\"email\";s:[1-9]+:\"jdoe@mail.com\"'

            OR  client.meta_key = 'ga_appointment_new_client'
            AND client.meta_value REGEXP '\"phone\";s:[1-9]+:\"999-656-5656\"'
        ";

Answer 1

看起来您需要在“或”命令附近添加适当的括号

$querystr = "SELECT $wpdb->posts.ID
        FROM
           $wpdb->posts,
           $wpdb->postmeta AS app_date,        
           $wpdb->postmeta AS provider,
           $wpdb->postmeta AS time1,
           $wpdb->postmeta AS time2,
           $wpdb->postmeta AS client

        WHERE
           $wpdb->posts.ID = app_date.post_id          
        AND
           $wpdb->posts.ID = provider.post_id
        AND
           $wpdb->posts.ID = time1.post_id
        AND
           $wpdb->posts.ID = time2.post_id
        AND
           $wpdb->posts.ID = client.post_id

        AND $wpdb->posts.post_type = 'ga_appointments'
        AND $wpdb->posts.post_status IN ('completed', 'publish', 'payment', 'pending')                 

        AND app_date.meta_key   = 'ga_appointment_date'
        AND app_date.meta_value = 2018-03-27

        AND provider.meta_key   = 'ga_appointment_provider'
        AND provider.meta_value = 55

        AND time1.meta_key = 'ga_appointment_time_end'
        AND time1.meta_value > 13:05

        AND time2.meta_key = 'ga_appointment_time'
        AND (time2.meta_value < 14:05

        OR  client.meta_key = 'ga_appointment_client')
        AND (client.meta_value REGEXP '\"email\";s:[1-9]+:\"jdoe@mail.com\"'

        OR  client.meta_key = 'ga_appointment_new_client')
        AND client.meta_value REGEXP '\"phone\";s:[1-9]+:\"999-656-5656\"'
    ";