减少mysql查询的执行时间 [英] reducing the mysql query execution time

问题描述

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).

这篇关于减少mysql查询的执行时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！