MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:walt Date:January 28 2003 9:04pm
Subject:mysql/innob optimizer problem with != and selecting other than primary key
View as plain text  
I ran into a problem a few weeks ago with a query that would take up to 2 
minutes to return 0 rows found when selecting anything other than the primary 
key (0 rows is correct). When I selected just the primary_key, the query 
would take less than 3 seconds. The 2 columns involved in the where part of 
the query are in a 2 column clustered index which aparently was being 
"ignored" when selecting anything other than the primary key and a  != in 
where part of the query. The actual table is 48 columns * 2,457,684 rows.
See below for better explanation.

NOTES - order_status can have 3 possible values (N, T, Y)

create table customer_orders (
order_id		int 		NOT NULL AUTO_INCREMENT,
customer_id 	char(8)	NOT NULL,
customer_session_id  varchar(30) NOT NULL,
order_status	char(1)	DEFAULT 'N'	NOT NULL,
cus_last_name varchar(20) NOT NULL,
cus_first_name varchar(20) NOT NULL,
cus_address1   varchar(50) NOT NULL,
cus_address2   varchar(50),
cus_zip		  varchar(10),
UNIQUE INDEX ORD_ID_IDX (order_id),
INDEX CUS_ID_SESSION_IDX (customer_id, customer_session_id),
INDEX CUS_ID_ORD_STAT_IDX (customer_id, order_status),
INDEX CUS_LAST_IDX(cus_last_name),
INDEX CUS_FIRST_IDX(cus_first_name)
)
TYPE=INNODB;


SELECTING ONLY THE PRIMARY KEY - 
"select order_id from customer_orders where customer_id = 'timma" 
and order_status != 'Y'
and order_status != 'T'";

query time < 3 seconds

SELECTING MORE THAN THE PRIMARY KEY -
"select order_id, cus_last_name, cus_first_name from customer_orders where
 customer_id = 'timma"
and order_status != 'Y' and order_status != 'T'";

query time =~ 2 minutes

SELECTING MORE THAN THE PRIMARY KEY BUT USING = INSTEAD OF !=
"select order_id, cus_last_name, cus_first_name from customer_orders where
customer_id = 'timma"
and order_status = 'N'";

query time < 3 seconds


Hope this helps!
mysql, query
-- 
Walter Anthony
System Administrator
National Electronic Attachment
Atlanta, Georgia 
1-800-782-5150 ext. 1608
 "If it's not broke....tweak it"
Thread
mysql/innob optimizer problem with != and selecting other than primary keywalt28 Jan