CODEWITHSUNDEEP
mysqlsqlperformancequery-optimization
user3895294
user3895294

Reputation: 21

MySQL Update query takes more time to update the table

I wrote two mysql Queries.

First one:

SELECT I.Status FROM Test_chq_out_more100 O, chqinformore100 I
WHERE I.ACC_No = O.ACC_No  AND I.Chq_No = O.Begin_Chq_no;

Second one:

SELECT I.Status FROM Test_chq_out_more100 O, chqinformore100 I
WHERE I.ACC_No = O.ACC_No  AND I.Chq_No = (O.Begin_Chq_no + 1);

First query takes 05 seconds to get the answer but Second query takes 1165 Seconds to return the result. How can I optimize second query to get result within 10 - 15 seconds.

There are 2.5M records in chqinformore100 table and 11000 records in Test_chq_out_more100 table.

Upvotes: 2

Views: 108

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Here is the second query, "fixed" to use proper join syntax (which shouldn't affect the query plan):

SELECT I.Status
FROM Test_chq_out_more100 O JOIN
     chqinformore100 I
     ON I.ACC_No = O.ACC_No  AND I.Chq_No = (O.Begin_Chq_no + 1);

This query can be optimized by reading the O table and looking up values in the I table. Hence, the index Test_chq_out_more100(ACC_No, Chq_No). However, it cannot take full advantage of an index on chqinformore100(ACC_No, Begin_Chq_no) because of the addition of 1. So, my recommendation is to do one of the following:

One: Keep the query as it is and create the index Test_chq_out_more100(ACC_No, Chq_No).

Two: Create the index chqinformore100(ACC_No, Begin_Chq_no) and change the query to:

SELECT I.Status
FROM Test_chq_out_more100 O JOIN
     chqinformore100 I
     ON I.ACC_No = O.ACC_No  AND I.Chq_No - 1 = O.Begin_Chq_no;

Upvotes: 2

Related Questions