Highrichshuai
2019-07-10 16:19
采纳率: 75%
浏览 180

怎么优化别人写好的sql语句?

中间那个子查询就将近30秒,整个语句好几分钟,不知道该怎么优化。求大神给个思路
SELECT DISTINCT result.* FROM(
SELECT
con.update_time,con.pk_id,con.origin_id,pro.project_no,con.title,pro.project_name
,com.company_name,comp.company_name
proxy_company_name,con.add_time,con.publish_time,con.state,xp.org_code,xp.org_name
FROM content con
LEFT JOIN bid_project pro ON con.origin_id = pro.pk_id
LEFT JOIN company_info com ON com.pk_id = pro.company_id
LEFT JOIN company_info comp ON comp.pk_id = pro.proxy_company_id
LEFT JOIN xpoplar_comp_info xp ON xp.org_code = con.org_code
LEFT JOIN flow_approve f ON f.source_pk_id = con.pk_id
WHERE con.type = 'N2')
result WHERE 1=1 AND STATE = '2' ORDER BY state ASC,project_no
DESC,publish_time DESC

+----+-------------+------------+--------+---------------+--------------+---------+--------------------------+------+---
| id | select_type | table      | type   | possible_keys | key          | key_len | ref                      | rows | Extra                                        |
+----+-------------+------------+--------+---------------+--------------+---------+--------------------------+------+-
|  1 | PRIMARY     | <derived2> | ref    | <auto_key0>   | <auto_key0>  | 5       | const                    |   10 | Using where; Using temporary; Using filesort |
|  2 | DERIVED     | con        | ALL    | NULL          | NULL         | NULL    | NULL                     | 7012 | Using where                                  |
|  2 | DERIVED     | pro        | eq_ref | PRIMARY       | PRIMARY      | 152     | ztb.con.origin_id        |    1 | Using where                                  |
|  2 | DERIVED     | com        | eq_ref | PRIMARY       | PRIMARY      | 152     | ztb.pro.company_id       |    1 | NULL                                         |
|  2 | DERIVED     | comp       | eq_ref | PRIMARY       | PRIMARY      | 152     | ztb.pro.proxy_company_id |    1 | NULL                                         |
|  2 | DERIVED     | xp         | ref    | ORG_CODE      | ORG_CODE     | 152     | ztb.con.org_code         |    1 | NULL                                         |
|  2 | DERIVED     | f          | ref    | source_pk_id  | source_pk_id | 153     | ztb.con.pk_id            |    1 | Using index                                  |
+----+-------------+------------+--------+---------------+--------------+---------+--------------------------+------+-
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • 德玛洗牙 2019-07-10 16:41
    已采纳

    你可以看看你这些表(bid_project、company_info、xpoplar_comp、flow_approve)有没有加索引,如果没有将这些表连接的字段加上索引会快一些

    点赞 打赏 评论

相关推荐 更多相似问题