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.
explain select  sum(principal_received) principal_received,
                sum(interest_received) interest_received,
                sum(advance_received) advance_received,
                sum(advance_adjusted) advance_adjusted
        from loan_receipts
        where lending_id in (35039,35040,35041,35042,35043,35044,35045,35185,35186,35460,35461,71912,71914,71915,71916) 
          and effective_on <= '2013-11-20'
          and deleted_at IS NULL 
           id: 1
  select_type: SIMPLE
        table: loan_receipts
         type: range
possible_keys: index_loan_receipts_lending,index_lending,index_loanreceipts_de,index_loanreceipts_del,index_loan_receipts_led
          key: index_lending
      key_len: 4
          ref: NULL
         rows: 233
       Extra: Using where
show create table loan_receipts;
 loan_receipts | CREATE TABLE `loan_receipts` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `principal_received` decimal(65,0) NOT NULL,
  `interest_received` decimal(65,0) NOT NULL,
  `advance_received` decimal(65,0) NOT NULL,
 `advance_adjusted` decimal(65,0) NOT NULL,
  `loan_recovery` decimal(65,0) NOT NULL,
  `is_advance_adjusted` tinyint(1) DEFAULT NULL,
  `currency` int(11) NOT NULL,
  `performed_at` int(11) NOT NULL,
  `accounted_at` int(11) NOT NULL,
  `effective_on` date NOT NULL,
  `created_at` datetime NOT NULL DEFAULT '2013-01-06 14:29:16',
  `lending_id` int(10) unsigned NOT NULL,
  `payment_transaction_id` int(10) unsigned DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
 PRIMARY KEY (`id`),
  KEY `index_loan_receipts_lending` (`lending_id`), 
  KEY `index_loanreceipts_de` (`deleted_at`,`effective_on`),
  KEY `index_loanreceipts_del` (`deleted_at`,`effective_on`,`lending_id`),  
) ENGINE=InnoDB AUTO_INCREMENT=1165270 DEFAULT CHARSET=utf8 |

I am using this query but it is not picking up index. Am I doing anything wrong?

these are all complete indexes of this query

KEY index_loan_receipts_payment_transaction (payment_transaction_id),
KEY index_loan_receipts_lending (lending_id),
KEY index_lending (lending_id,advance_adjusted,advance_received,effective_on,deleted_at),
KEY index_loan_receipts (accounted_at,is_advance_adjusted,effective_on,deleted_at),
KEY index_ied (is_advance_adjusted,effective_on,deleted_at),
KEY index_loanreceipts_pprir (payment_transaction_id,principal_received,interest_received),
KEY index_loanreceipts_de (deleted_at,effective_on),
KEY index_loanreceipts_del (deleted_at,effective_on,lending_id)

share|improve this question
2  
Under possible_keys, the query plan lists two indexes that don't appear in your table definition: index_lending (which the optimiser has selected for use, as indicated under key) and index_loan_receipts_led. Where are these indexes defined? –  eggyal Nov 20 '13 at 7:21
    
i didn't put out complete list of indexes added them now please check it once –  kareem Nov 21 '13 at 6:39

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.