CODEWITHSUNDEEP
mysql
sankar
sankar

Reputation: 357

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'

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

Upvotes: 4

Views: 1692

Answers (3)

NedStarkOfWinterfell
NedStarkOfWinterfell

Reputation: 5163

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

Upvotes: 4

xkeshav
xkeshav

Reputation: 54032

reference from you @comment you said u did not create any index on any table. So CREATE INDEX on those column on which are used in WHERE and ON condition

in your case create index on 

mt.Test_ID, 
mv.Test_ID, 
mv.Point_ID,
mp.Point_ID, 
mtp.Test_ID ,
mp.group_name ,
mtp.Test_Plan_Doc_No

Here I Rewrite your Query:

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 USING(Test_ID)            
       INNER JOIN measurement_point mp USING (Point_ID)
       INNER JOIN measurement_test_plan mtp USING(Test_ID)
WHERE  mtp.Test_Plan_Doc_No IN ( 'test1', 'test2' )
       AND mp.Group_Name = 'gp_name'

Upvotes: 2

Endy
Endy

Reputation: 696

Make sure you have proper indexes for each column used in the WHERE clause.

You can use a query analyzer to see whether or not the indexes are used.

Upvotes: 0

Related Questions