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" --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
