How can I optimise sql query

user_10364960 0
Viewed 74

I'm new to programming, coming from accounting background. I have this query I have written which works but very slow. I am wondering if there is a way I could write it to optimize its speed.

Tables I am fetching from:

Master Table

enter image description here

Incident Table enter image description here

Results Table enter image description here

Accreditation Table enter image description here

Here is the query:

SELECT a.lgName AS lgname
  , a.wardName AS wardName
  , a.pUnitName AS pUnitName
  , SUM(a.pvc_collected) AS pvc
  , SUM(a.voter_reg_no) AS purvs
  , (   SELECT COUNT(pUnitName)
        FROM master
        WHERE wardName = a.wardName) AS No_Poll_Unitss
  , (   SELECT COUNT(reportedpu)
        FROM master
        WHERE wardName = a.wardName
        AND reportedpu = 1) AS reportedpu
  , (   SELECT COUNT(pUnitName)
        FROM master
        WHERE pUnitName = a.pUnitName) AS No_Poll_Unitss
  , (   SELECT COUNT(reportedpu)
        FROM master
        WHERE pUnitName = a.pUnitName
        AND reportedpu = 1) AS reportedpu
  , (   SELECT SUM(total)
        FROM accreditation
        WHERE pu_name IN (   SELECT pUnitName
                             FROM master
                             WHERE pUnitName = a.pUnitName)) AS acr
  , (   SELECT SUM(cno)
        FROM res
        WHERE pUnitName IN (   SELECT pUnitName
                               FROM master
                               WHERE pUnitName = a.pUnitName)
        AND category = 'tvc') AS tvc
  , (   SELECT SUM(cno)
        FROM res
        WHERE pUnitName IN (   SELECT pUnitName
                               FROM master
                               WHERE pUnitName = a.pUnitName)
        AND category = 'apc') AS apc
  , (   SELECT SUM(cno)
        FROM res
        WHERE pUnitName IN (   SELECT pUnitName
                               FROM master
                               WHERE pUnitName = a.pUnitName)
        AND category = 'ivc') AS ivc
  , (   SELECT SUM(cno)
        FROM res
        WHERE pUnitName IN (   SELECT pUnitName
                               FROM master
                               WHERE pUnitName = a.pUnitName)
        AND category = 'pdp') AS pdp
  , (   SELECT SUM(cno)
        FROM res
        WHERE pUnitName IN (   SELECT pUnitName
                               FROM master
                               WHERE pUnitName = a.pUnitName)
        AND category = 'lp') AS lp
  , (   SELECT SUM(cno)
        FROM res
        WHERE pUnitName IN (   SELECT pUnitName
                               FROM master
                               WHERE pUnitName = a.pUnitName)
        AND category = 'adc') AS adc
  , (   SELECT SUM(cno)
        FROM res
        WHERE pUnitName IN (   SELECT pUnitName
                               FROM master
                               WHERE pUnitName = a.pUnitName)
        AND category = 'sdp') AS sdp
  , (   SELECT SUM(cno)
        FROM res
        WHERE pUnitName IN (   SELECT pUnitName
                               FROM master
                               WHERE pUnitName = a.pUnitName)
        AND category = 'adp') AS adp
  , (   SELECT SUM(cno)
        FROM res
        WHERE pUnitName IN (   SELECT pUnitName
                               FROM master
                               WHERE pUnitName = a.pUnitName)
        AND category = 'other') AS oth
FROM master AS a
GROUP BY a.pUnitName, a.userCode
ORDER BY a.userCode ASC;
mysql sql
3 Answers

Query is quite tricky, but first thing I'd try would be putting all of these subqueries into one subquery in such a way:

SELECT *
FROM master AS A
CROSS JOIN (   SELECT SUM(IF(category = 'adp', cno, 0)) AS adp
                 -- All other conditions
                 , SUM(IF(category = 'other', cno, 0)) AS other
               FROM res AS R
               WHERE pUnitName IN (   SELECT pUnitName
                                      FROM master
                                      WHERE pUnitName = A.pUnitName)
               AND category = 'other') AS oth;

This should run select from res table just once and calculate sum for every condition you want. Also, it should certainly be possible to remove that pUnitName condition from subquery, but it needs more knowledge on your dataset.

user_36800980

The best approach in our case is to aggregate before doing the joins. You have four different aggregations that you need:

  • master by pUnitName
  • master by WardName
  • accreditation by pUnitName
  • res by pUnitName`

Then LEFT JOIN these together:

SELECT m.pUnitName, m.userCode,
       SUM(m.pvc_collected) as pvc,
       SUM(m.voter_reg_no) AS purvs,
       mw.Num_Poll_Units, mw.reportedpu,
       mu.Num_Poll_Units, mu.reportedpu,
       a.acr,
       r.tvc, r.apc, . . .
FROM master m LEFT JOIN
     (SELECT m2.wardName, COUNT(*) as Num_Poll_Units,
             SUM(m2.reportedpu = 1) as reportedpu
      FROM master m2
      GROUP BY m2.wardName
     ) mw
     USING (wardname) LEFT JOIN
     (SELECT m2.pUnitName, COUNT(*) as Num_Poll_Units,
             SUM(m2.reportedpu = 1) as reportedpu
      FROM master m2
      GROUP BY m2.pUnitName
     ) mu
     USING (pUnitName) LEFT JOIN
     (SELECT a.pu_name, SUM(a.total) as acr
      FROM accreditation a
      GROUP BY a.pu_name
     ) a
     ON a.pu_name = m.pUnitName LEFT JOIN
     (SELECT r.pUnitName,
             SUM(CASE WHEN category = 'tvc' THEN cno ELSE 0 END) as tvc,
             SUM(CASE WHEN category = 'apc' THEN cno ELSE 0 END) as apc,
             . . .
      FROM res r
      GROUP BY r.pUnitNmae
     ) r
     USING (pUnitName)
GROUP BY m.pUnitName, m.userCode,
         mw.Num_Poll_Units, mw.reportedpu,
         mu.Num_Poll_Units, mu.reportedpu,
         a.acr,
         r.tvc, r.apc, . . .
ORDER BY m.userCode ASC;

Note: The outermost query is not aggregating by WardName. It is not clear what you really want for these columns. You may need to do the following:

  • Remove mw.Num_Poll_Units and mw.reportedpu from the GROUP BY.
  • Change the SELECT to SUM(mw.Num_Poll_Units) or AVG(mw.Num_Poll_Units) or whatever appropriate function should be used.
user_11440350

The query has to be optimize completely. I mention some point to make it optimize:

  1. use cross apply instead sub queries
  2. use window functions
  3. also you can create some indexes

you can search on the internet for the topics. or you can ask your questions.

user_50934630
Related