Optimize a complex SQL query

I'm sure there is a better way to do this. It looks at an applications table and collects all applications of a certain status for each job.

So it looks like this:

pending | screened | interviewed | accepted | offer | hired | job title
0         0          0             0          0       2       dirt mover
2         0          1             1          0       1       tree planter
7         2          1             1          1       3       hole digger

Here is the sql (with extra union columns removed for readability, if you can call this query readable)

select sum(pending) as pending, sum(screened) as screened, sum(interviewed) 
as interviewed, sum(accepted) as accepted, sum(offer) as offer, sum(hired) 
as hired, sum(declined) as declined, sum(rejected) as rejected, title, jobid
 from 
(

(select count(j.job_id) as pending, j.title as title, j.job_id as jobid from 
applications a, jobs j where j.job_id = a.job_id and status = 'Pending' group by 
j.job_id)

union

(select count(j.job_id) as screened, j.title as title, j.job_id as jobid from 
applications a, jobs j where j.job_id = a.job_id and status = 'Screened' group by 
j.job_id)

union

(select count(j.job_id) as interviewed, j.title as title, j.job_id as jobid from 
applications a, jobs j where j.job_id = a.job_id and status = 'Interviewed' group by 
j.job_id)

union

(select count(j.job_id) as accepted, j.title as title, j.job_id as jobid from 
applications a, jobs j where j.job_id = a.job_id and status = 'Accepted' group by 
j.job_id)

union

(select count(j.job_id) as offer, j.title as title, j.job_id as jobid from开发者_JAVA百科 
applications a, jobs j where j.job_id = a.job_id and status = 'Offer Made' group by 
j.job_id)

union

(select count(j.job_id) as hired, j.title as title, j.job_id as jobid from 
applications a, jobs j where j.job_id = a.job_id and status = 'Offer Accepted' group
 by j.job_id)

union

(select count(j.job_id) as declined, j.title as title, j.job_id as jobid from 
applications a, jobs j where j.job_id = a.job_id and status = 'Offer Declined' group
 by j.job_id)

union

(select count(j.job_id) as rejected, j.title as title, j.job_id as jobid from 
applications a, jobs j where j.job_id = a.job_id and status = 'Rejected' group by 
j.job_id)

) as summ group by title order by title

Here is SHOW CREATE TABLE applications

CREATE TABLE IF NOT EXISTS `applications` (
  `app_id` int(5) NOT NULL auto_increment,
  `job_id` int(5) NOT NULL,
  `status` varchar(25) NOT NULL,
  `reviewed` datetime NOT NULL,
  PRIMARY KEY  (`app_id`),
  UNIQUE KEY `app_id` (`app_id`),
  KEY `job_id` (`job_id`),
  KEY `status` (`status`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2720 ;

Here is SHOW CREATE TABLE jobs

CREATE TABLE IF NOT EXISTS `jobs` (
  `job_id` int(5) NOT NULL auto_increment,
  `title` varchar(25) NOT NULL,
  PRIMARY KEY  (`app_id`),
  KEY `job_id` (`job_id`),
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

---

It looks like you are doing a PIVOT / cross tab query. Something like this should do the trick.

SELECT COUNT(CASE
               WHEN status = 'Pending' THEN 1
             END) AS pending,
       COUNT(CASE
               WHEN status = 'Screened' THEN 1
             END) AS screened,
       /*Remaining ones left as an exercise for the reader*/
       title
FROM   applications a
       JOIN jobs j
         ON j.job_id = a.job_id
GROUP  BY title
ORDER  BY title  

---

First, some things to note:

1. Don't use cross joins (Theta joins). This really slows your query down.:

   SELECT * FROM table_1,table_2 WHERE table_1.id = table_2.t1_id

Use instead:

SELECT * FROM table_1 INNER JOIN table_2 ON(table_1.id = table_2.t1_id)

1. PRIMARY KEYs are already UNIQUE KEYS

Now, here's the way I'd do it:

SELECT
    SUM(a.app_id),
    a.status
FROM
    applications a INNER JOIN   
    jobs j ON (j.job_id=a.job_id)
GROUP BY
    j.job_id,
    a.status

For this simple query you should get something similar to:

JOB_TITLE    | STATUS        |   COUNT
dirt mover   | pending       |    0
dirt mover   | screened      |    0
dirt mover   | interviewed   |    0
dirt mover   | accepted      |    0
dirt mover   | offer         |    0
dirt mover   | hired         |    2

tree planter | pending       |    2
tree planter | screened      |    0
tree planter | interviewed   |    1
tree planter | accepted      |    1
tree planter | offer         |    0
tree planter | hired         |    1
...

Note that the title is repeated, but that's not a problem, becouse you can get the same information. The query will be several times faster, and simpler.