Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2011
    Posts
    1

    Unanswered: SQL running slow

    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;

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    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`;
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •