SELECT
`ac`.`user_name` AS `user_name`,
`ac`.`user_account` AS `user_account`,
`ac`.`user_type` AS `user_type`,
`ac`.`detail_addr` AS `detail_addr`,
`bl`.`create_name` AS `create_name`,
`bl`.`create_date` AS `create_date`,
`bl`.`bill_code` AS `bill_code`,
`bl`.`this_m` AS `this_m`,
`bl`.`balance_m` AS `balance_m`,
`bl`.`bill_type` AS `bill_type`,
`od`.`step1_num` AS `step1_num`,
`od`.`step2_num` AS `step2_num`,
`od`.`step3_num` AS `step3_num`,
`od`.`step_price` AS `step_price`,
`bl`.`sys_company_code` AS `sys_company_code`
FROM
(
(
bl
LEFT JOIN od ON (
(
`bl`.`order_code` = `od`.`order_code` and `bl`.bill_type != '200'
)
)
)
JOIN ac ON (
(
`bl`.`user_account` = `ac`.`user_account` and `bl`.bill_type != '200'
)
)
)
ORDER BY
`bl`.`id` DESC 有三个表 ac 、bl、od这样查询太慢了,不知有什么方法可以提高显示速度
`ac`.`user_name` AS `user_name`,
`ac`.`user_account` AS `user_account`,
`ac`.`user_type` AS `user_type`,
`ac`.`detail_addr` AS `detail_addr`,
`bl`.`create_name` AS `create_name`,
`bl`.`create_date` AS `create_date`,
`bl`.`bill_code` AS `bill_code`,
`bl`.`this_m` AS `this_m`,
`bl`.`balance_m` AS `balance_m`,
`bl`.`bill_type` AS `bill_type`,
`od`.`step1_num` AS `step1_num`,
`od`.`step2_num` AS `step2_num`,
`od`.`step3_num` AS `step3_num`,
`od`.`step_price` AS `step_price`,
`bl`.`sys_company_code` AS `sys_company_code`
FROM
(
(
bl
LEFT JOIN od ON (
(
`bl`.`order_code` = `od`.`order_code` and `bl`.bill_type != '200'
)
)
)
JOIN ac ON (
(
`bl`.`user_account` = `ac`.`user_account` and `bl`.bill_type != '200'
)
)
)
ORDER BY
`bl`.`id` DESC 有三个表 ac 、bl、od这样查询太慢了,不知有什么方法可以提高显示速度
SELECT `ac`.`user_name` AS `user_name`,
`ac`.`user_account` AS `user_account`,
`ac`.`user_type` AS `user_type`,
`ac`.`detail_addr` AS `detail_addr`,
`bl`.`create_name` AS `create_name`,
`bl`.`create_date` AS `create_date`,
`bl`.`bill_code` AS `bill_code`,
`bl`.`this_m` AS `this_m`,
`bl`.`balance_m` AS `balance_m`,
`bl`.`bill_type` AS `bill_type`,
`od`.`step1_num` AS `step1_num`,
`od`.`step2_num` AS `step2_num`,
`od`.`step3_num` AS `step3_num`,
`od`.`step_price` AS `step_price`,
`bl`.`sys_company_code` AS `sys_company_code`
FROM bl
LEFT JOIN od
ON `bl`.`order_code` = `od`.`order_code`
AND `bl`.bill_type != '200'
JOIN ac
ON `bl`.`user_account` = `ac`.`user_account`
AND `bl`.bill_type != '200'
ORDER BY
`bl`.`id` DESC一般来说, != 不符合SARG标准, 容易导致表扫描。
另外:
1. 你这3个表各有多少数据?
2. 最终的结果有多当数据?
3. 每个表上的索引的情况是怎么样的?
show index from bl;
show index from od;
show index from ac;
只看你的SQL, 基本上没有过滤, 你要一次性显示这么多数据出来吗?
使用!=就不会使用index了。