Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
SELECT j.job_id,j.last_updated,j.job_title,j.status,
(SELECT COUNT(*) FROM candidates WHERE job_id=j.job_id) resumes_count,
(SELECT COUNT(*) FROM candidates c WHERE job_id=j.job_id AND c.ref_type=1) auto_resumes_count,
(SELECT COUNT(*) FROM candidates c WHERE job_id=j.job_id AND c.ref_type=0) self_resumes_count,
(SELECT COUNT(*) FROM candidates c WHERE job_id=j.job_id AND c.ref_type=-1) paging_resumes_count,
(SELECT SUM(views) FROM job_views WHERE job_id=j.job_id) job_views,
(SELECT posts FROM autopost_jobs WHERE job_id=j.job_id) autopost_count
FROM jobs j WHERE j.user_id={$userId} ORDER BY j.last_updated DESC,STATUS

Table structure:

| jobs  | CREATE TABLE `jobs` (
  `job_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(20) NOT NULL,
  `job_title` varchar(450) NOT NULL,
  `job_desc` varchar(5000) NOT NULL,
  `sjob_data` text NOT NULL,
  `job_requirements` varchar(5000) DEFAULT NULL,
  `years_of_experience` varchar(100) NOT NULL,
  `location_id` int(11) unsigned NOT NULL,
  `department_id` int(11) NOT NULL,
  `role_id` int(10) unsigned NOT NULL,
  `job_company_name_alt` varchar(100) DEFAULT NULL,
  `is_show_company` tinyint(1) NOT NULL DEFAULT '1',
  `status` tinyint(2) NOT NULL DEFAULT '0' COMMENT '0=inactive,1=active,2=deleted',
  `job_reward` varchar(128) DEFAULT NULL,
  `job_lang` int(11) NOT NULL DEFAULT '0' COMMENT '0 - Ru 1 - En',
  `job_photo` varchar(100) DEFAULT NULL,
  `last_updated` datetime NOT NULL,
  `is_hot_job` tinyint(1) NOT NULL DEFAULT '0',
  `job_internal_id` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`job_id`)
) ENGINE=MyISAM AUTO_INCREMENT=1794 DEFAULT CHARSET=utf8 |

| candidates | CREATE TABLE `candidates` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `hr_id` int(11) DEFAULT NULL,
  `job_id` int(10) unsigned NOT NULL DEFAULT '0',
  `through_user_id` int(11) NOT NULL,
  `ref_type` tinyint(4) NOT NULL COMMENT '0 = personal , 1 = automatic , 2 = file uploaded , 3 = lead sent',
  `media_id` tinyint(2) NOT NULL DEFAULT '-1',
  `status_id` int(11) DEFAULT NULL,
  `cv_filename` varchar(250) DEFAULT NULL,
  `lead_url` varchar(256) DEFAULT NULL,
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0',
  `is_viewed` tinyint(1) NOT NULL DEFAULT '0',
  `is_hired` tinyint(1) NOT NULL DEFAULT '0',
  `is_employee_note` tinyint(4) NOT NULL DEFAULT '0',
  `note` varchar(1000) NOT NULL,
  `last_updated` datetime NOT NULL,
  `device_type` int(1) NOT NULL DEFAULT '0' COMMENT '0 - web, 1 - mobile',
  `third_party_approval` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `setIndex` (`user_id`,`job_id`,`through_user_id`)
) ENGINE=MyISAM AUTO_INCREMENT=20012 DEFAULT CHARSET=utf8 |
| autopost_jobs | CREATE TABLE `autopost_jobs` (
  `job_id` int(11) NOT NULL,
  `hr_id` int(11) NOT NULL,
  `posts` bigint(20) DEFAULT '0',
  `media_id` int(11) NOT NULL,
  `last_user_id` int(11) DEFAULT NULL,
  `last_updated` datetime DEFAULT NULL,
  `inserted_date` datetime DEFAULT NULL,
  PRIMARY KEY (`job_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
| job_views | CREATE TABLE `job_views` (
  `user_id` int(11) NOT NULL,
  `hr_id` int(11) NOT NULL,
  `job_id` int(11) NOT NULL,
  `media_id` int(11) NOT NULL,
  `post_type_id` int(11) NOT NULL COMMENT '0 - self, 1 - auto, -1 - undefined ',
  `date` date NOT NULL,
  `views` bigint(20) NOT NULL DEFAULT '0',
  `last_time` time NOT NULL,
  PRIMARY KEY (`user_id`,`hr_id`,`job_id`,`media_id`,`date`,`post_type_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |

This query just hangs, what could be the reason?, how can I optimize it

share|improve this question

closed as off-topic by Mark Storey-Smith, RolandoMySQLDBA, Max Vernon, Kin, billinkc Oct 25 '13 at 3:03

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Mark Storey-Smith, Max Vernon, Kin, billinkc
If this question can be reworded to fit the rules in the help center, please edit the question.

    
I think you may get better performance from joining all four tables by job_id and then grouping by the same column. –  mustaccio Oct 17 '13 at 14:16
1  
Please add the SHOW CREATE TABLE jobs; output - and the same for candidates and job_views and autopost_jobs –  ypercube Oct 17 '13 at 14:20
1  
For us to have a decent idea of your situation, we need SHOW CREATE TABLE instead of SHOW COLUMNS IN.../DESCRIBE/EXPLAIN ... without the indexes there's too much we don't know. Also, the output from EXPLAIN SELECT ... your query will show the query plan the optimizer is using. –  Michael - sqlbot Oct 17 '13 at 14:21
1  
And how many rows do you expect from the query (i.e. how many jobs are there for a user, on average)? –  ypercube Oct 17 '13 at 14:23
    
Edited, now contains all of the required information –  Oleg Tikhonov Oct 17 '13 at 14:32

3 Answers 3

Indexes to add:

Table: candidates
  (job_id, ref_type)

Table: job_views
  (job_id, views)

Table: autopost_jobs
  (job_id, posts)

Then run again the queries and post the updated EXPLAIN

share|improve this answer
    
Thank you for you answer, I will try it on Mon, and will definitely get you posted –  Oleg Tikhonov Oct 18 '13 at 20:54

I think you forgot SUM() aggregate function in this subquery :

(SELECT SUM(posts) FROM autopost_jobs WHERE job_id=j.job_id) autopost_count

I would use a CTE to query candidates table.

share|improve this answer
    
MySQL does not have CTEs. –  ypercube Oct 17 '13 at 20:27
    
As far as I see it the both ways it gives the same result. Unless I m missing something. Thanks for taking the time to post your Answer –  Oleg Tikhonov Oct 18 '13 at 20:55

I think the following version should have a better execution plan :

SELECT j.job_id,j.last_updated,j.job_title,j.status,
    MAX(CASE WHEN  ref_type = 1 THEN  resumes_count END) as auto_resumes_count ,
    MAX(CASE WHEN  ref_type = 0 THEN  resumes_count END )  as self_resumes_count ,
    MAX(CASE WHEN  ref_type = -1 THEN  resumes_count END )as paging_resumes_count,
    MAX(CASE WHEN  ref_type IS NULL THEN  resumes_count END )as resumes_count,
    (SELECT SUM(views) FROM job_views WHERE job_id=j.job_id) job_views,
    (SELECT posts FROM autopost_jobs WHERE job_id=j.job_id) autopost_count


    FROM jobs j 
    LEFT JOIN 
    (
        SELECT job_id, ref_type , COUNT(1) as resumes_count 
        FROM 
        candidates 
        GROUP BY job_id,ref_type
        WITH ROLLUP
    )c ON (c.job_id = j.job_id)

    WHERE j.user_id={$userId} 
    GROUP BY j.job_id
    ORDER BY j.last_updated DESC,STATUS;

You may try adding a filter to subquery in left join as well (I'm not sure if value of job.user_id corresponds candidates.user_id, so I put 2 versions of filter in comments) :

        SELECT j.job_id,j.last_updated,j.job_title,j.status,
        MAX(CASE WHEN  ref_type = 1 THEN  resumes_count END) as auto_resumes_count ,
        MAX(CASE WHEN  ref_type = 0 THEN  resumes_count END )  as self_resumes_count ,
        MAX(CASE WHEN  ref_type = -1 THEN  resumes_count END )as paging_resumes_count,
        MAX(CASE WHEN  ref_type IS NULL THEN  resumes_count END )as resumes_count,
        (SELECT SUM(views) FROM job_views WHERE job_id=j.job_id) job_views,
        (SELECT posts FROM autopost_jobs WHERE job_id=j.job_id) autopost_count


        FROM jobs j 
        LEFT JOIN 
        (
            SELECT job_id, ref_type , COUNT(1) as resumes_count 
            FROM 
            candidates c  
            -- if c.user_id is the same as j.user_id you can add following filter:
            ---------------------------
             -- WHERE c.user_id = {user_id}  
            ---------------------------
            -- if it's different , try this filter :
            ---------------------------
             -- WHERE EXISTS (SELECT NULL FROM job j1 WHERE j1.job_id =c.job_id
             --   AND j1.user_id ={user_id})
            --------------------------- 
            GROUP BY job_id,ref_type
            WITH ROLLUP
        )c ON (c.job_id = j.job_id)

        WHERE j.user_id={$userId} 
        GROUP BY j.job_id
        ORDER BY j.last_updated DESC,STATUS;

I don't have a chance to check actual execution plans, but it should be better because candidate tables is scanned once compared to 4 times in the question.

share|improve this answer
    
Thank you for you answer, I will try it on Mon, and will definitely get you posted –  Oleg Tikhonov Oct 18 '13 at 20:53

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