SQL running slow

EmSiden · #1 (**permalink**) 12-06-11, 23:08

Dear all,

I am sorry if this topic is wrong because I am new in this forum.
Could you help me with the SQL process. I am using MySQL and connect with Joomla. I want to count all active and inactive job in my database but amount of record too much, So it process too slow. Do you have any trick to solve?

Thank in advance, your answer will appropriate..!!!

My code:

Code:

SELECT d.`id`, d.`name`, d.`email` AS `contact_email`, d.`notify`, d.`notify_admin`, d.`logo`, d.`status`
			,(SELECT COUNT(j.`id`) FROM `jos_jobboard_jobs` AS j WHERE j.`department` = d.`id` AND j.`published` = 1) AS active
			,(SELECT COUNT(j.`id`) FROM `jos_jobboard_jobs` AS j WHERE j.`department` = d.`id` AND j.`published` = 0) AS inactive
		    FROM `jos_jobboard_departments` AS d;

it-iss.com · #2 (**permalink**) 12-07-11, 02:45

Try something like this:

Code:

SELECT d.`id`, d.`name`, d.`email` AS `contact_email`, d.`notify`, d.`notify_admin`, d.`logo`, d.`status`, SUM(IF(j.published=1,1,0)) active, SUM(IF(j.published=0,1,0)) inactive
FROM `jos_jobboard_departments` AS d
LEFT JOIN jos_jobboard_jobs AS j ON j.department = d.id
GROUP BY d.`id`, d.`name`, d.`email` AS `contact_email`, d.`notify`, d.`notify_admin`, d.`logo`, d.`status`;

Internet Services

Web Development

Digital Marketing

Consumer Tech