MySQL Forums
Forum List  »  Optimizer & Parser

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
3178
October 30, 2009 08:45AM
1758
October 31, 2009 10:19AM
1799
October 31, 2009 03:49PM
1754
November 01, 2009 09:49PM
1731
November 02, 2009 10:45AM
1738
November 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.