CODEWITHSUNDEEP
mysqlsql
Samir
Samir

Reputation: 121

How to Optimise this sql query

This query is taking 3 seconds and i want to make it run more quickly. Please provide any suggestions 

SELECT Concat(e.estimate1, '-', e.estimate2) AS estimateid,
       e.estimatetype,
       e.createdby,
       e.estimateid AS estID,
       e.`layoutnumber`,
       sd.specno,
       sd.samplenumber,
       sd.numberon,
       c.customerid,
       c.custprosname,
       c.`custtype`,
       (SELECT Count(*)
        FROM  (SELECT e.estimate1
               FROM   `simpleestimatedetails` sd,
                      estimatemaster e,
                      `vcustomer_prospect` c
               WHERE  c.customerid IN ( e.customernumber, e.prospectnumber )
                      AND ( e.estimate1 LIKE '%1%' )
                      AND ( sd.`simpleestid` = e.estimateid )) AS counter) AS
       counter
FROM   `simpleestimatedetails` sd,
       estimatemaster e,
       `vcustomer_prospect` c
WHERE  c.customerid IN ( e.customernumber, e.prospectnumber )
       AND ( e.estimate1 LIKE '%1%' )
       AND ( sd.`simpleestid` = e.estimateid );

Upvotes: 2

Views: 120

Answers (2)

Romesh
Romesh

Reputation: 2274

SELECT Concat(e.estimate1, '-', e.estimate2) AS estimateid,
       e.estimatetype,
       e.createdby,
       e.estimateid AS estID,
       e.`layoutnumber`,
       sd.specno,
       sd.samplenumber,
       sd.numberon,
       c.customerid,
       c.custprosname,
       c.`custtype`
  FROM estimatemaster e Inner Join
       `vcustomer_prospect` c
    On c.customerid IN ( e.customernumber, e.prospectnumber )
 Inner Join `simpleestimatedetails` sd
    On sd.`simpleestid` = e.estimateid
 WHERE e.estimate1 LIKE '%1%'

Note : I have removed the counter column. If you are doing it from the some front end end then you can get the counter value by checking RowsAffected or RowCount or RecordsCount or Somthing Similar property of query component.

Upvotes: 1

Amit Vikram
Amit Vikram

Reputation: 402

In Your SQL Query the 'Counter' is calling redundant join of multiple table.

Please ignore the counter column and try to fetch the values as total number of rows returned from the SQl query in last.

Hope this will enhance the Query performance. and you will get desired result by following Query

select concat(e.estimate1,'-',e.estimate2) as estimateid,
         e.estimatetype,
         e.CreatedBy,
         e.EstimateID as estID,
         e.`LayoutNumber`,
          sd.specNo,
          sd.SampleNumber,
          sd.NumberON, c.customerid,
          c.CustProsName,
          c.`CustType`
             from `simpleestimatedetails` sd,
              estimatemaster e,
              `vcustomer_prospect` c
             where c.customerid in (e.customernumber,e.ProspectNumber)
             and (e.estimate1 like '%1%')
              and (sd.`SimpleEstID`=e.estimateid);

note: the total number of rows will give you value of counter

Upvotes: 1

Related Questions