Query is so slow
Hi everyone,
I have this table schema
CREATE
TABLE transaction
(
id VARCHAR(20) NOT NULL,
client_id bigint NOT NULL,
transaction_id VARCHAR(20) NOT NULL,
donor_id bigint,
donor_deceased CHAR(1),
legal_amount DECIMAL(18,2),
credit_amount DECIMAL(18,2),
date_record DATETIME,
gift_type_code VARCHAR(6),
pledge_amount_paid DECIMAL(18,2),
appeal_code VARCHAR(30),
fiscal_year VARCHAR(4),
annual_flag CHAR(1),
campaign_flag CHAR(1),
cashin_ind CHAR(1),
commitment_ind CHAR(1),
pledge_payment_ind CHAR(1),
PRIMARY KEY (id),
INDEX client_id (client_id),
INDEX in_client_donor_id (client_id, donor_id),
INDEX in_client_af_ind (client_id, annual_flag),
INDEX in_client_date_record (client_id, date_record),
INDEX in_client_cashin_ind (client_id, cashin_ind),
INDEX in_client_credit_amount (client_id, credit_amount),
INDEX in_client_legal_amount (client_id, legal_amount),
INDEX commitment_ind (commitment_ind)
)
ENGINE=InnoDB DEFAULT CHARSET=latin1
when I execute "explain
SELECT
*
FROM
gift
WHERE
client_id=15
AND annual_flag='A'
AND cashin_ind='Y'
AND credit_amount>0
AND date_record>='2009-08-30'
AND date_record<'2010-10-30'"
the explain return
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE gift range in_client_annual_flag,in_client_cashin_ind,in_client_donor_id,client_id,in_client_date_record in_client_date_record 16 (null) 67872 Using where
Can you give me some advise to improve my indexes. It take almost 40 second to finish this query.
Thanks,
Fitsum