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'

hello guys.

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

But i have a problem as follows:

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.

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

解决方案

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