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.

For the following table structure in MySQL 5.1.49:

CREATE TABLE `leads` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `institution_id` int(10) unsigned NOT NULL,
  `lender_id` int(10) unsigned NOT NULL,
  `product_id` int(10) unsigned NOT NULL,
  `client_id` int(10) unsigned NOT NULL,
  `contract_id` int(10) unsigned DEFAULT NULL,
  `employee_id` int(11) unsigned DEFAULT NULL,
  `create_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `inquiry_date` timestamp NULL DEFAULT NULL,
  `claimed_date` timestamp NULL DEFAULT NULL,
  `refunded` timestamp NULL DEFAULT NULL,
  `price` decimal(10,2) unsigned NOT NULL,
  `downloaded` int(11) NOT NULL DEFAULT '0',
  `status` enum('in_review','declined','pre-approved') DEFAULT NULL,
  `amount` bigint(20) DEFAULT NULL,
  `response` longtext,
  `pushed` tinyint(1) NOT NULL DEFAULT '0',
  `priority` tinyint(2) NOT NULL,
  `disabled` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `unq_lead` (`institution_id`,`client_id`,`product_id`),
  KEY `status` (`status`),
  KEY `fk-product-leads` (`product_id`),
  KEY `fk-contract-leads` (`contract_id`),
  KEY `fk-lender-leads` (`lender_id`),
  KEY `fk-client-leads` (`client_id`),
  KEY `fk-employee-leads` (`employee_id`),
  CONSTRAINT `fk-client-leads` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk-contract-leads` FOREIGN KEY (`contract_id`) REFERENCES `contracts` (`id`),
  CONSTRAINT `fk-employee-leads` FOREIGN KEY (`employee_id`) REFERENCES `users` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `fk-institution-leads` FOREIGN KEY (`institution_id`) REFERENCES `institutions` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk-lender-leads` FOREIGN KEY (`lender_id`) REFERENCES `lenders` (`id`),
  CONSTRAINT `fk-product-leads` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5930472 DEFAULT CHARSET=utf8

Is there any way to further optimize this query:

SELECT * 
FROM leads
INNER JOIN (
    SELECT MIN(id) AS 'min_id', `leads`.`institution_id`, `leads`.`client_id`
    FROM `leads`
    WHERE (claimed_date IS NOT NULL)
    AND institution_id = 224
    GROUP BY `institution_id`,`client_id`
    HAVING (COUNT(*) > 0)
) AS `cl` ON leads.institution_id = cl.institution_id AND leads.client_id = cl.client_id AND leads.id != cl.min_id
WHERE (leads.disabled = 0);

These are the results I get currently from EXPLAIN:

id  select_type  table       type  possible_keys                                       key       key_len  ref                             rows  Extra        
1   PRIMARY      <derived2>  ALL                                                                                                          5832               
1   PRIMARY      leads       ref   unq_lead,fk-client-leads,fk-institution-leads,test  unq_lead  8        cl.institution_id,cl.client_id  1     Using where  
2   DERIVED      leads       ref   unq_lead,fk-institution-leads                       unq_lead  4                                        6013  Using where 

UPDATE

So I have rewritten the query so that it appears like so:

SELECT *
FROM leads
INNER JOIN (
SELECT MIN(id) AS `min_id`, `leads`.`institution_id`, `leads`.`client_id`
FROM `leads`
WHERE (claimed_date IS NOT NULL)
AND (institution_id = 224)
GROUP BY `client_id`
) AS `cl` ON leads.institution_id = cl.institution_id AND leads.client_id = cl.client_id AND leads.id <> cl.min_id
WHERE (leads.disabled = 0)
AND (leads.institution_id = 224);

I have also tried adding the following additional indexes:

ALTER TABLE leads
    ADD INDEX test (disabled, institution_id, client_id),
    ADD INDEX test2 (claimed_date, institution_id, client_id);

But I still get the following from EXPLAIN:

id  select_type  table       type  possible_keys                  key       key_len  ref                 rows   Extra        
1   PRIMARY      <derived2>  ALL                                                                         5718   Using where  
1   PRIMARY      leads       ref   unq_lead,fk-client-leads,test  unq_lead  8        const,cl.client_id  1      Using where  
2   DERIVED      leads       ref   unq_lead,test2                 unq_lead  4                            12304  Using where  
share|improve this question
    
Why the funny leads.id != cl.min_id condition? –  ypercube Jun 1 '12 at 19:07
    
What indexes do you have? –  user1240 Jun 2 '12 at 3:02

1 Answer 1

  • Remove the HAVING COUNT(*) > 0. It's useless, no row will have a count of 0 after a group by.

  • Change the GROUP BY to: GROUP BY client_id. Grouping by institution_id is not needed, you already have a WHERE condition that narrows it one value.

  • As @HLGEM suggested, remove the select * and use a list of fields that you need. Right now you are repeating data in the client_id field and that is wasteful of server and network resources.

So the query becomes:

SELECT le.*                  --- only the fields you need here
                             --- for example `institution_id` is 224, so
                             --- there is no need to include that
FROM leads AS le
  INNER JOIN (
    SELECT MIN(id) AS min_id, institution_id, client_id
    FROM leads 
    WHERE claimed_date IS NOT NULL
      AND institution_id = 224
    GROUP BY client_id
  ) AS cl 
      ON  le.institution_id = cl.institution_id 
      AND le.client_id = cl.client_id 
      AND le.id <> cl.min_id
WHERE le.disabled = 0 ;
  • Then add an index on (claimed_date, institution_id, client_id) to speed up the nested subquery.

  • If that doesn't really improve the speed much, I think an index on (disabled, institution_id, client_id) would help the joining.

You could also rewrite the query as:

SELECT le.* 
FROM leads AS le
  INNER JOIN (
    SELECT MIN(id) AS min_id, client_id
    FROM leads 
    WHERE claimed_date IS NOT NULL
      AND institution_id = 224
    GROUP BY client_id
  ) AS cl 
      ON  le.client_id = cl.client_id 
      AND le.id <> cl.min_id
WHERE le.disabled = 0 
  AND le.institution_id = 224;
share|improve this answer
1  
I would also remove the select * and use a list of fields. Right now you are repeating data in the client_id field and that is wasteful of server and network resources. –  HLGEM Jun 5 '12 at 18:53
    
@HLGEM: corrected. –  ypercube Jun 5 '12 at 19:44

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.