php - SQL_BIG_SELECTS，优化我的查询

Question

我正在使用 HTML 选择输入，启用了多项选择，我正在查看选定的选项并尝试处理我的 SQL ... 但是，我收到来自 SQL 的警告，告诉我如果我的选择语句是的，我应该使用“SET SQL_BIG_SELECTS = 1”，因为 JOIN 超出了限制？

这是我的查询:

"
        SELECT wposts.*, wpostmeta.* 
        FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta, $wpdb->postmeta wpostmeta2, $wpdb->postmeta wpostmeta3 
        WHERE wposts.ID = wpostmeta.post_id 
            AND wposts.ID = wpostmeta2.post_id 
            AND wposts.ID = wpostmeta3.post_id 
            AND wpostmeta.meta_key = 'listing_subtype' 
                AND wpostmeta.meta_value = '$search_home_type' 
            AND wpostmeta2.meta_key = 'map_area' 
                AND ";

                $count = 0;
                foreach ($params['search_map_area'] as $map_area) :
                    if ( $count != 0 ) :
                        $querystr .= "OR ";
                    endif;
                    $querystr .= "wpostmeta2.meta_value = '$map_area' ";
                    $count++;
                endforeach;

    $querystr .= "AND wpostmeta3.meta_key = 'price_current' 
                AND wpostmeta3.meta_value BETWEEN $search_price_min AND $search_price_max 
            AND wposts.post_status = 'publish' 
            AND wposts.post_type = 'vreb_property' 
        ORDER BY wposts.post_date DESC 
        LIMIT 0, 20
        ";

有没有更好的查询方式？这是多个 OR wpostmeta2.meta_value = '$map_area' 似乎导致我的脚本超时......

Answer 1

    FROM $wpdb->posts wposts, 
        INNER JOIN $wpdb->postmeta wpostmeta ON wposts.ID = wpostmeta.post_id
        INNER JOIN $wpdb->postmeta wpostmeta2 ON wposts.ID = wpostmeta2.post_id
        INNER JOIN $wpdb->postmeta wpostmeta3 ON wposts.ID = wpostmeta3.post_id 
    WHERE ...

使用 JOIN ... ON 可能比在 WHERE 类中拥有所有 ID 关系更可取。

---

你真的应该使用参数化查询，这样你就不会向 SQL 注入(inject)敞开心扉，因为你似乎没有正确地转义你使用的任何字符串。 http://www.php.net/manual/en/mysqli.real-escape-string.php

但是，如果你在开发私有(private)/内部应用程序和安全性，那该死的......

            $temp = "'" . implode("','", $params['search_map_area']) . "'";
            if($temp != "''")
                $querystr .= "wpostmeta2.meta_value IN ($temp)";
            else
                $querystr .= " 1=1 ";

使用 IN 关键字而不是多个“OR col = 'val'”也是首选。