CODEWITHSUNDEEP
mysqlsql
Ammar
Ammar

Reputation: 683

how to fast my mysql query?

hello here is mysql query.

SELECT pbi.iBillItemID,
   pbi.vItemCode,
   pbi.vDescription,
   if(
      pb.iLab = 0,
      pbi.iQty,
      (  pbi.iQty
       - ifnull(
            (SELECT sum(p_bi.iQty)
               FROM patientbillitem p_bi, patientbillpayment p_b
              WHERE     p_bi.vRefReceipt = p_b.vReceiptNo
                    AND p_b.iBillNo = p_iBillNo
                    AND p_bi.vItemCode = pbi.vItemCode),
            0)))
   iQty
   FROM patientbillitem pbi,
   patientbillpayment pb,
   patient_service ps,
   patient_service_category psc
   WHERE pbi.iBillNo = pb.iBillNo
   AND pb.iBillNo = p_iBillNo
   AND pbi.vItemCode = ps.iServiceID
   AND ps.iCategoryID = psc.Asc_ID
   AND pbi.iPatID = p_iPatID

above mysql query performing to slow actually p_bi.vItemCode = pbi.vItemCode using in this query is varchar field thats why it is taking too much time to execute i try to add full text index on this column then it working very fastly but problem is full text index only support with constant string. can any one give me solution for this query thank you.

Upvotes: 0

Views: 63

Answers (2)

Mehrnoosh
Mehrnoosh

Reputation: 899

1) index on vRefReceipt, iBillNo, vItemCode

2) make the subquery as a temp table or CTE (Common Table Expression) then select from it.

Upvotes: 1

Vijunav Vastivch
Vijunav Vastivch

Reputation: 4191

Just:

Make an index to all of our primary key which is used by your query then simulate how long it takes than before and after.

Upvotes: 0

Related Questions