在C#查询MySql里如下42000错误异常,怎么解决?

这是错误信息:
#42000The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay

我是在查询:
C# code

string cmdStr = "SELECT * FROM view_import_log_new " + operateTimeClause + idClause + operatorClause + " ORDER BY Log_Sn DESC";

            MySqlCommand cmd = new MySqlCommand(cmdStr);

            if (operatorname != "") cmd.Parameters.Add("?operator", "%" + operatorname + "%");

            DataSet dsImportLog = GetTable(cmd, "ImportLog");


建的一个视图view_import_log_new,视图代码如下:
SQL code

SELECT
    `i`.`Log_Sn` AS `Log_Sn`,
    `i`.`File_ID` AS `File_ID`,
    `i`.`Inpatient_ID` AS `Inpatient_ID`,
    `i`.`U_ID` AS `U_ID`,
    `i`.`MR_ID` AS `MR_ID`,
    `i`.`Operator` AS `Operator_ID`,
    (
        CASE
        WHEN isnull(`o`.`Operator_Name`)THEN
            `i`.`Operator`
        ELSE
            `o`.`Operator_Name`
        END
    )AS `Operator_Name`,
    `i`.`Operate_Time` AS `Operate_Time`,
    `i`.`Action` AS `Action`,
    `i`.`insertflag` AS `insertflag`,
    `h`.`Pack_ID` AS `Pack_ID`,
    `h`.`Barcode_ID` AS `Barcode_ID`,
    `h`.`Block_ID` AS `Block_ID`,
    `h`.`HR_ID` AS `HR_ID`,
    `h`.`HR_Count` AS `HR_Count`,
    `h`.`Hospitalize_ID` AS `Hospitalize_ID`,
    `h`.`Hospitalize_Count` AS `Hospitalize_Count`,
    (
        CASE
        WHEN(`h`.`Hospitalize_Count` = 0)THEN
            _utf8 '-'
        WHEN(
            `h`.`Hospitalize_ID` = _utf8 ''
        )THEN
            _utf8 '-'
        ELSE
            `h`.`Hospitalize_Count`
        END
    )AS `Hospitalize_Count_Text`,
    `h`.`Patient_ID` AS `Patient_ID`,
    `h`.`Patient_Name` AS `Patient_Name`,
    `h`.`Patient_Gender` AS `Patient_Gender`,
    `h`.`In_Date` AS `In_Date`,
    `h`.`In_Dept` AS `In_Dept_Code`,
    (
        CASE
        WHEN isnull(`s1`.`Name`)THEN
            `h`.`In_Dept`
        ELSE
            `s1`.`Name`
        END
    )AS `In_Dept_Name`,
    `h`.`Out_Date` AS `Out_Date`,
    `h`.`Out_Dept` AS `Out_Dept_Code`,
    (
        CASE
        WHEN isnull(`s2`.`Name`)THEN
            `h`.`Out_Dept`
        ELSE
            `s2`.`Name`
        END
    )AS `Out_Dept_Name`,
    `h`.`Out_Status` AS `Out_Status`,
    `h`.`Is_Dead` AS `Is_Dead`,
    `h`.`Create_Time` AS `Create_Time`,
    `h`.`Imp_Feature` AS `Imp_Feature`,
    (
        CASE
        WHEN(`h`.`Is_Proof` = 0)THEN
            _utf8 '0'
        ELSE
            _utf8 '1'
        END
    )AS `Is_Proof`,
    (
        CASE
        WHEN(`h`.`Is_Code` = 0)THEN
            _utf8 '0'
        ELSE
            _utf8 '1'
        END
    )AS `Is_Code`,
    (
        CASE
        WHEN(`h`.`Is_QC` = 0)THEN
            _utf8 '0'
        ELSE
            _utf8 '1'
        END
    )AS `Is_QC`,
    (
        CASE
        WHEN(`h`.`Is_Burn` = 0)THEN
            _utf8 '0'
        ELSE
            _utf8 '1'
        END
    )AS `Is_Burn`,
    (
        CASE
        WHEN(`h`.`Is_Pack` = 0)THEN
            _utf8 '0'
        ELSE
            _utf8 '1'
        END
    )AS `Is_Pack`,
    (
        CASE
        WHEN(`h`.`Is_Seal` = 0)THEN
            _utf8 '0'
        ELSE
            _utf8 '1'
        END
    )AS `Is_Seal`,
    `h`.`Physical_Status` AS `Physical_Status`,
    `h`.`Virtual_Status` AS `Virtual_Status`,
    `h`.`Recover_Feature` AS `Recover_Feature_Code`,
    (
        CASE
        WHEN isnull(`s3`.`Name`)THEN
            `h`.`Recover_Feature`
        ELSE
            `s3`.`Name`
        END
    )AS `Recover_Feature_Name`,
    (
        CASE
        WHEN(`h`.`Is_Valid` = 0)THEN
            _utf8 '0'
        ELSE
            _utf8 '1'
        END
    )AS `Is_Valid`,
    `h`.`source` AS `source`
FROM
    (
        (
            (
                (
                    (
                        `import_log` `i`
                        LEFT JOIN `operator` `o` ON(
                            (`i`.`Operator` = `o`.`RFID`)
                        )
                    )
                    LEFT JOIN `health_record` `h` ON((`i`.`U_ID` = `h`.`MR_ID`))
                )
                LEFT JOIN `sys_dict` `s1` ON(
                    (
                        (`h`.`In_Dept` = `s1`.`Code`)
                        AND(`s1`.`Type` = _utf8 '科室')
                    )
                )
            )
            LEFT JOIN `sys_dict` `s2` ON(
                (
                    (`h`.`Out_Dept` = `s2`.`Code`)
                    AND(`s2`.`Type` = _utf8 '科室')
                )
            )
        )
        LEFT JOIN `sys_dict` `s3` ON(
            (
                (
                    `h`.`Recover_Feature` = `s3`.`Code`
                )
                AND(
                    `s3`.`Type` = _utf8 '回收特征'
                )
            )
        )
    )

作者: chyude   发布时间: 2011-05-30

MYSQL版本?已经提示SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=

作者: WWWWA   发布时间: 2011-05-30

从错误信息上来看,是你的查询中连接表的数量过多了。 理论上你可以去加大 MAX_JOIN_SIZE ,但如果这么多JOIN,显然效率会很差。

作者: ACMAIN_CHM   发布时间: 2011-05-30

怎样加大MAX_JOIN_SIZE ?呢配置文件里.ini里? 
引用 2 楼 acmain_chm 的回复:

从错误信息上来看,是你的查询中连接表的数量过多了。 理论上你可以去加大 MAX_JOIN_SIZE ,但如果这么多JOIN,显然效率会很差。

作者: chyude   发布时间: 2011-05-30

你可以打开你的 my.cnf 或者 my.ini 文件, 然后添加参数在 [mysqld] 段中。

max_join_size=1000

作者: ACMAIN_CHM   发布时间: 2011-05-30

引用 4 楼 acmain_chm 的回复:
你可以打开你的 my.cnf 或者 my.ini 文件, 然后添加参数在 [mysqld] 段中。

max_join_size=1000


这个我加在my.ini里了,还是同样的报错,还有什么办法吗?

作者: chyude   发布时间: 2011-05-30