CODEWITHSUNDEEP
mysqlsqljoin
Bharath Kumar J
Bharath Kumar J

Reputation: 23

Executing SQL JOIN Takes To Long

I have three tables: patient, obg and doctor.

Please help me to understand why it's taking so much time (more than 1 minute). When I do not use searchtxt I am getting less time however, if I use searchtxt in some fields I am experiencing delays in retrieving the result.

SELECT 
            a.ID AS Pt_ID,
            a.Pt_Name,
            a.Pt_Sex,
            Date_Format(a.Pt_DOB,'%Y/%m/%d') as Pt_DOB,
            a.Pt_Age,
            a.Pt_Opt_HrF,
            a.Pt_HrF_Name,
            a.Pt_Mobile,
            a.Pt_Address,
            b.ID as R_ID,
            Date_Format(b.RP_DATE,'%Y/%m/%d') as RP_DATE,
            b.B_HISTORY,
            b.Ref_Dr_ID,
            Date_Format(b.LMP,'%Y/%m/%d') as LMP,
            b.H_O_G,
            b.P_U_S,
            b.RP_TY,
            b.Ref_Dr_ID,
            c.Dr_Name
 FROM  
    patient a,
    obg b,
    doctor c 
where 

    a.DEL_FLG='N' and b.DEL_FLG='N' and 
    (b.RP_DATE BETWEEN STR_TO_DATE('".$_POST['frm_dt']."', '%Y/%m/%d') AND STR_TO_DATE('".$_POST['to_dt']."', '%Y/%m/%d')) and 
   a.ID  LIKE '%".$_POST['searchtxt']."%'
OR b.ID  LIKE '%".$_POST['searchtxt']."%'      
OR a.Pt_Name  LIKE '%".$_POST['searchtxt']."%' 
OR a.Pt_DOB = STR_TO_DATE(".$_POST['searchtxt'].",'%Y/%m/%d')
OR a.Pt_Address LIKE '%".$_POST['searchtxt']."%'
OR a.Pt_HrF_Name LIKE '%".$_POST['searchtxt']."%'
OR a.Pt_Mobile LIKE '%".$_POST['searchtxt']."%' 
a.ID = b.Pt_ID and 
    b.Ref_Dr_ID = c.ID 
order by b.CRTD_DT Desc

Upvotes: 0

Views: 45

Answers (1)

user5992977
user5992977

Reputation:

I can suggest something like this which will allow you to use like only once without OR's by using concat function - change your query to something like this:

SELECT  *
FROM  
    patient a,
    obg b,
    doctor c 
where 
    a.DEL_FLG='N' and b.DEL_FLG='N'
    AND (b.RP_DATE BETWEEN STR_TO_DATE('".$_POST['frm_dt']."', '%Y/%m/%d') AND STR_TO_DATE('".$_POST['to_dt']."', '%Y/%m/%d')) 
    AND (concat(b.id,'-',a.id,'-',a.Pt_Name,'-',a.Pt_Address,'-',
           a.Pt_HrF_Name,'-',a.Pt_Mobile) LIKE  '%".$_POST['searchtxt']."%'
    OR a.Pt_DOB = STR_TO_DATE(".$_POST['searchtxt'].",'%Y/%m/%d')) 
    AND a.ID = b.Pt_ID  
    AND b.Ref_Dr_ID = c.ID 
order by b.CRTD_DT Desc

Also, I think you had a few minor issues with the AND OR AND.. you should use parenthes in this places to make sure that it follow your logic, like in my answer

Upvotes: 1

Related Questions