Optimize a complex SQL query

Question

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 
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;

What do you need? What are you expecting to get from the query? — santiagobasulto
– santiagobasulto, Commented Sep 14, 2011 at 20:47
A result set faster than 11 seconds. The resulting table is at the top of my question. — Chris G.
– Chris G., Commented Sep 14, 2011 at 20:53

Martin Smith · Accepted Answer · 2011-09-14 20:58:34Z

2

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

answered Sep 14, 2011 at 20:58

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

santiagobasulto · Accepted Answer · 2011-09-14 21:16:16Z

First, some things to note:

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)

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.

Collectives™ on Stack Overflow

Optimize a complex SQL query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related