MySQL Forums :: Optimizer & Parser :: Query is so slow


Advanced Search

Query is so slow
Posted by: Fitsum Tesfagiorgis ()
Date: October 30, 2009 08:45AM

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

Options: ReplyQuote


Subject Views Written By Posted
Query is so slow 3149 Fitsum Tesfagiorgis 10/30/2009 08:45AM
Re: Query is so slow 1748 Rick James 10/31/2009 10:19AM
Re: Query is so slow 1790 Fitsum Tesfagiorgis 10/31/2009 03:49PM
Re: Query is so slow 1743 Fitsum Tesfagiorgis 11/01/2009 09:49PM
Re: Query is so slow 1720 Rick James 11/02/2009 10:45AM
Re: Query is so slow 1730 Fitsum Tesfagiorgis 11/02/2009 02:22PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.