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]
