减少mysql查询的执行时间

本文介绍了减少mysql查询的执行时间的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 SELECT mt.Test_ID, mtp.Test_Plan_Doc_No, mp.Group_Name, mp.Point_Name, mp.Limit_1, mp.Limit_2, mp.Datatype, mt.Start_Date, mv.Measurement_Value, mv.Status_Value FROM measurement_test mt INNER JOIN measurement_values mv ON mt.Test_ID = mv.Test_ID INNER JOIN measurement_point mp ON mv.Point_ID = mp.Point_ID INNER JOIN measurement_test_plan mtp ON mtp.Test_ID = mt.Test_ID WHERE mtp.Test_Plan_Doc_No IN ( 'test1', 'test2' ) AND mp.Group_Name = 'gp_name'

大家好.

以上是我正在执行的用于创建视图的查询.

The above is the query that i am executing to create a view.

但是我有一个问题，如下:

But i have a problem as follows:

测量值表大约有8200万行. 测量点表大约有500万行 测量测试计划和测量测试表大约有9000-100000行.

the measurement value table has around 82 million rows. the measurement point table has around say 5 million rows measurement test plan and measurement test tables have around 9000 - 100000 rows.

我的问题是，当我执行上述查询时，执行时间为8分钟，并且生成的结果只有400行.

My problem is when i execute the above query the execution time is 8 minutes and the result that is generated has only 400 rows.

有什么方法可以减少上述查询的执行时间?

Is there any possible way to reduce the execution time of the above query?

注意:我在网页中使用上面的查询

NOTE : I am using the above query in my web page

推荐答案

首先，为了获得高效的join，您应该以增加的行数顺序保留表.这样可以大大减少行扫描的次数.因此，对于您的查询，join的顺序应为measurement_test mt natural join measurement_point mp natural join measurement_values mv.还要确保联接列上已定义索引，并且它们具有完全相同的数据类型.在MySQL中，char(15)和varchar(15)被认为是相似的，但char(15)和varchar(10)却不一样.

First off, for an efficient join, you should keep the tables in increasing order of number of rows. This reduces the number of row scans drastically. So for your query, the join order should be measurement_test mt natural join measurement_point mp natural join measurement_values mv. Also ensure that the join columns have indexes defined on them, and they have exactly identical datatypes. char(15) and varchar(15) are considered similar in MySQL, but not char(15) and varchar(10).