0

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.

0

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`)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.