MySQL – Optimize subquery in Select, where and order by

MySQLoptimizationsubquery

Im running a query like below.

SELECT DISTINCT c.`id`
    , c.`job-cat`
    , (
        SELECT COUNT(*)
        FROM `jobs` j
        WHERE j.`mul_category` IN (c.`id`)
            AND j.`ending` >= DATE (NOW())
        ) AS jobCount
FROM `jobscat` c
WHERE (
        SELECT COUNT(*)
        FROM `jobs` j
        WHERE j.`mul_category` IN (c.`id`)
            AND j.`ending` >= DATE (NOW())
        ) > '0'
ORDER BY (
        SELECT COUNT(*)
        FROM `jobs` j
        WHERE j.`mul_category` IN (c.`id`)
            AND j.`ending` >= DATE (NOW())
        ) DESC

from the above query this 

SELECT COUNT(*)
        FROM `jobs` j
        WHERE j.`mul_category` IN (c.`id`)
            AND j.`ending` >= DATE (NOW())

repeated thrice, so my query planner also needs to execute this query 3times.

Since alias names will not work in where, so how can I optimize this query instead of running thrice I want to run only one time.

In order by I can use alias to reduce one step.

Best Answer

Turn it around so that you start with the grouping:

SELECT  c.id, c.`job-cat`, x.jobCount
    FROM  
    (
        SELECT  mul_category, COUNT(*) AS jobCount
            FROM  jobs
            WHERE  ending >= CURDATE()
            GROUP BY  mul_category 
    ) AS x
    JOIN  jobscat AS c  ON c.id = x.mul_category

Indexes:

jobs:  (mul_category, ending)
jobscat:  (id, `job-cat`)
Related Question