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 am using this simple select query which selects data from table inner joins and using group by clause. Here is the query ..I have defined indexes for the where clause and group by clause. But some how mysql doesn't pick up this index . I have tried interchanging conditions inside where clause but it still doesn't pick up.

select l.status, 
       l1.lending_id, l1.status loan_status, l1.new_funder_id, 
       l1.accounted_at, l1.loan_base_schedule_id,  
       l.closed_on_date,l.disbursed_amount   
  from lending_infos l1     
 inner join (select lending_id, 
                   max(id) max_id from lending_infos use index(index_lending_infos_lending) 
             where effective_on <= '2013-11-28' group by lending_id
           ) l2 on l2.max_id = l1.id inner join lendings l on l.id = l2.lending_id 
where l1.new_funder_id in (2) group by l.id;  

The explain statement shows that the index used is something else other than index i created

*************************** 1. row ***************************  
           id: 1  
  select_type: PRIMARY  
        table: <derived2>  
         type: ALL  
possible_keys: NULL  
          key: NULL  
      key_len: NULL  
          ref: NULL  
         rows: 234630  
        Extra: Using temporary; Using filesort  
*************************** 2. row ***************************  
           id: 1  
  select_type: PRIMARY  
        table: l1  
         type: eq_ref  
possible_keys: PRIMARY,index_lending_infos_new_funder  
          key: PRIMARY  
      key_len: 4  
          ref: l2.max_id  
         rows: 1  
        Extra: Using where  
*************************** 3. row ***************************  
           id: 1  
  select_type: PRIMARY  
        table: l  
         type: eq_ref  
possible_keys: PRIMARY,index_lendings_il,index_lendings_isd,index_lendings_id  
          key: PRIMARY  
      key_len: 4  
          ref: l2.lending_id  
         rows: 1  
        Extra:   
*************************** 4. row ***************************  
           id: 2  
  select_type: DERIVED  
        table: lending_infos  
         type: index  
possible_keys: NULL  
          key: index_lending_infos_lending  
      key_len: 4  
          ref: NULL  
         rows: 285229  

Below is the structure of my table with indices.

show create table lending_infos;  

 lending_infos | CREATE TABLE `lending_infos` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `status` int(11) NOT NULL DEFAULT '1',
  `accounted_at` int(11) DEFAULT NULL,
  `administered_at` int(11) DEFAULT NULL,
  `effective_on` date DEFAULT NULL,
  `loan_base_schedule_id` int(10) unsigned NOT NULL,
  `client_id` int(10) unsigned NOT NULL,
  `lending_product_id` int(10) unsigned NOT NULL,
  `new_funder_id` int(10) unsigned NOT NULL,
  `lending_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `index_lending_infos_lending` (`lending_id`),
  KEY `index_lending_infos_lending_product` (`lending_product_id`),
  KEY `index_lending_infos_client` (`client_id`),
  KEY `index_lending_infos_loan_base_schedule` (`loan_base_schedule_id`),
  KEY `index_lending_infos_new_funder` (`new_funder_id`),
  KEY `index_lendinginfos_le` (`lending_id`,`id`,`effective_on`)

Another table structure with indices.

CREATE TABLE `lendings` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `lan` varchar(40) NOT NULL,
  `applied_amount` decimal(65,0) NOT NULL,
  `currency` int(11) NOT NULL,
  `applied_on_date` date NOT NULL,
  `approved_amount` decimal(65,0) DEFAULT NULL,
  `disbursed_amount` decimal(65,0) DEFAULT NULL,
  `scheduled_disbursal_date` date NOT NULL,
  `scheduled_first_repayment_date` date NOT NULL,
  `approved_on_date` date DEFAULT NULL,
  `disbursal_date` date DEFAULT NULL,
  `repaid_on_date` date DEFAULT NULL,
  `rejected_on_date` date DEFAULT NULL,
  `write_off_on_date` date DEFAULT NULL,
  `write_off_approve` tinyint(1) DEFAULT NULL,
  `write_off_approve_on_date` date DEFAULT NULL,
  `preclosed_on_date` date DEFAULT NULL,
  `repayment_frequency` int(11) NOT NULL,
  `tenure` int(10) unsigned NOT NULL,
  `administered_at_origin` int(11) NOT NULL,
  `accounted_at_origin` int(11) NOT NULL,
  `applied_by_staff` int(11) NOT NULL,
  `approved_by_staff` int(11) DEFAULT NULL,
  `disbursed_by_staff` int(11) DEFAULT NULL,
  `rejected_by_staff` int(11) DEFAULT NULL,
  `written_off_by_staff` int(11) DEFAULT NULL,
  `preclosed_by_staff` int(11) DEFAULT NULL,
  `repaid_by_staff` int(11) DEFAULT NULL,
  `recorded_by_user` int(11) NOT NULL,
  `repayment_allocation_strategy` int(11) NOT NULL,
  `status` int(11) NOT NULL DEFAULT '1',
  `loan_purpose` varchar(50) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT '2013-01-06 14:29:16',
  `updated_at` datetime NOT NULL DEFAULT '2013-01-06 14:29:16',
  `deleted_at` datetime DEFAULT NULL,
  `cycle_number` int(11) NOT NULL DEFAULT '1',
  `disbursement_mode` int(11) NOT NULL DEFAULT '1',
  `cheque_number` int(11) DEFAULT NULL,
  `lending_product_id` int(10) unsigned NOT NULL,
  `upload_id` int(10) unsigned DEFAULT NULL,
  `loan_borrower_id` int(10) unsigned NOT NULL,
  `mark_write_off_accounting` tinyint(1) DEFAULT '0',
  `closed_on_date` date DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `lan_UNIQUE` (`lan`),
  KEY `index_lendings_lending_product` (`lending_product_id`),
  KEY `index_lendings_loan_borrower` (`loan_borrower_id`),
  KEY `index_lendings_upload` (`upload_id`),
  KEY `index_lendings_cycle_number` (`cycle_number`),
  KEY `disdate` (`disbursal_date`),
  KEY `index_sa` (`status`,`accounted_at_origin`),
  KEY `index_lendings_iaa` (`accounted_at_origin`,`id`,`administered_at_origin`),
  KEY `index_lendings_ds` (`deleted_at`,`scheduled_disbursal_date`),
  KEY `index_lendings_il` (`id`,`lending_product_id`),
  KEY `index_lendings_di` (`disbursal_date`,`id`),
  KEY `index_lendings_isd` (`id`,`accounted_at_origin`,`status`,`deleted_at`),
  KEY `index_lendings_id` (`id`)

I am not able to understand why this simple query doesn't pick up index I defined for columns in where clause

share|improve this question
    
+1 kareem for providing pretty solid information.. but can you also post the output off the derived table.. explain select lending_id, max(id) max_id from lending_infos use index(index_lending_infos_lending) where effective_on <= '2013-11-28' group by lending_id as extra information (should the the same as row 1 off your already posted explain because this derived table is executed first) –  Raymond Nijland Nov 30 '13 at 19:57
add comment

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.