Hi Rob, Since you're using 4.0.16, sounds like you are experiencing its optimizer bug. From the ChangeLog for 4.0.17 (not released yet):
* Fixed optimizer bug, introduced in 4.0.16, when REF access plan was preferred to more efficient RANGE on another column. So hopefully the problem only exists in 4.0.16. Until you can use another version, I guess you'll have to use USE INDEX. :-/ Hope that helps. Matt ----- Original Message ----- From: "Rob Brackett" Sent: Tuesday, November 18, 2003 4:33 PM Subject: Optimizer Troubles > I've got a table with two indices -- one is a datetime with massive > cardinality, and another is a varchar with only 9 distinct values. The > optimizer seems to choose the varchar over the datetime in all cases > unless I specifically say USE INDEX(). Is there some way to make MySQL > "smarter" in this case? I tried analyzing the table, that didn't work. > Maybe this is a case where I have to say "that's why USE INDEX() > exists". Strange thing is I didn't have this problem in 3.23 (running > 4.0.16 now). Thanks for your help. > > -Rob- > > P.S. Here are some details: > > SELECT count(*) FROM table1 WHERE date1 > NOW() - INTERVAL 1 HOUR AND > status='status1'; > > 1 row in set (1 min 14.18 sec) > > SELECT count(*) FROM table1 USE INDEX(date1) WHERE date1 > NOW() - > INTERVAL 1 HOUR AND status='status1'; > > 1 row in set (0.00 sec) > > EXPLAIN SELECT count(*) FROM table1 WHERE date1 > NOW() - INTERVAL 1 > HOUR AND status='status1'; > > Table: table1 > Type: ref > Possible_keys: date1,status > Key: status > Key_len: 17 > Ref: const > Rows: 4548428 > Extra: Using where > > EXPLAIN SELECT count(*) FROM table1 USE INDEX(date1) WHERE date1 > NOW() > - INTERVAL 1 HOUR AND status='status1'; > > Table: table1 > Type: range > Possible_keys: date1 > Key: date1 > Key_len: 9 > Ref: NULL > Rows: 16105 > Extra: Using where > > show keys from table1; > > Table: table1 > Non_unique: 0 > Key_name: PRIMARY > Seq_in_index: 1 > Column_name: id > Collation: A > Cardinality: 7911940 > Sub_part: NULL > Packed: NULL > Null: NO > Index_type: BTREE > Comment: > > Table: table1 > Non_unique: 1 > Key_name: date1 > Seq_in_index: 1 > Column_name: date1 > Collation: A > Cardinality: 3955970 > Sub_part: NULL > Packed: NULL > Null: YES > Index_type: BTREE > Comment: > > Table: table1 > Non_unique: 1 > Key_name: status > Seq_in_index: 1 > Column_name: status > Collation: A > Cardinality: 9 > Sub_part: NULL > Packed: NULL > Null: YES > Index_type: BTREE > Comment: -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
