一条SQL查询语句的优化(效率提升1000倍)

By admin, 19 五月, 2015

weike_student_wrong_questions表大概有150万行数据,其中(tid, cid)做了索引,weike_exam_files不到100行,tid做了索引。下面一条语句执行时间大概是1.3秒。

 

SELECT weike_student_wrong_questions.tid, name, exam_time,
update_time, cid
FROM weike_student_wrong_questions
INNER JOIN weike_exam_files
ON weike_student_wrong_questions.tid = weike_exam_files.tid
GROUP BY weike_student_wrong_questions.tid, cid
ORDER BY exam_time DESC, cid;

把语句换成下面之后,查询效率大概提高了1000倍。 

     

      SELECT name, exam_time, update_time, e.tid, cid
      FROM weike_exam_files e
      INNER JOIN
        (SELECT tid, cid
         FROM weike_student_wrong_questions
         GROUP BY tid, cid) tc
      ON e.tid = tc.tid
      ORDER BY exam_time DESC, cid;

标签

评论

Restricted HTML

  • 允许的HTML标签:<a href hreflang> <em> <strong> <cite> <blockquote cite> <code> <ul type> <ol start type> <li> <dl> <dt> <dd> <h2 id> <h3 id> <h4 id> <h5 id> <h6 id> <img src>
  • 自动断行和分段。
  • 网页和电子邮件地址自动转换为链接。
验证码
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.
请输入"Drupal10"