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.

I have a query with following explain plan

EXPLAIN SELECT COUNT(applicant.id) as applicantCount,
    application.name,applicant.application_id,application.id 
FROM  applicant 
  LEFT JOIN application 
    ON applicant.application_id = application.id 
  LEFT JOIN calendar 
    ON applicant.application_calendar_id = calendar.id 
  LEFT JOIN application_category 
    ON application_category.id = calendar.category_id 
WHERE application_category.id = '886760e6d0b6ab306b0914a60ac4265e'  
GROUP BY application.name\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: application_category
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 38
          ref: const
         rows: 1
        Extra: Using index; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: calendar
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
        Extra: Using where
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: applicant
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2333634
        Extra: Using where; Using join buffer
*************************** 4. row ***************************
           id: 1
  select_type: SIMPLE
        table: application
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 38
          ref: eRecuitment_TestQueries.applicant.application_id
         rows: 1
        Extra: 
4 rows in set (0.00 sec)

Query takes 30 seconds to complete where tables application, application_category and calendar are master tables which have 3 to 4 records only. The applicant table has about 2400000 records.

Is there any other approach to rewrite/optimize this query?

Index details

  • Table application

    PRIMARY KEY (`id`)
    
  • Table applicant

    PRIMARY KEY (`id`),
    KEY `idx_abdul_test` (`application_id`)
    
  • Table Calendar

    PRIMARY KEY (`id`)
    
  • Table application_category

    PRIMARY KEY (`id`)
    
share|improve this question
    
Does application.name have unique constraint (or values so a constraint can be applied)? Is there an index on that column? –  ypercube Aug 12 '13 at 13:48
    
Yes the names are unique,I had already tried that index but no change in query performance. –  Abdul Manaf Aug 12 '13 at 13:51
    
I see no index on the applicant (application_calendar_id) column either. And the last 2 LEFT joins could be converted to INNER joins (the WHERE condition makes it equivalent.) –  ypercube Aug 12 '13 at 13:54
    
Actually application_calendar_id and application_id have only 4 unique records so should i create index on application_calendar_id ? –  Abdul Manaf Aug 12 '13 at 13:56
    
I guess you mean that applicant.application_calendar_id has only 4 different valus in the 2.4M rows. –  ypercube Aug 12 '13 at 14:03
show 1 more comment

1 Answer 1

The last two LEFT joins can be rewritten as INNER joins (due to the WHERE condition on the application_category table.

If application (name) is indeed unique, there is no difference if you group by that column or by application (id) or even by applicant (application_id) - the last one provided there is a FOREIGN KEY constraint from applicant towards application.

So, you could add an index on applicant (application_calendar_id, application_id)

and rewrite the query as:

SELECT 
    COUNT(*) AS applicantCount,
    application.name, 
    applicant.application_id,
    application.id 
FROM  applicant 
  LEFT JOIN application 
    ON applicant.application_id = application.id 
  JOIN calendar 
    ON applicant.application_calendar_id = calendar.id 
  JOIN application_category 
    ON application_category.id = calendar.category_id 
WHERE application_category.id = '886760e6d0b6ab306b0914a60ac4265e'  
GROUP BY applicant.application_id ;
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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