Better indexing in 5.1

Apart from partitioning that helps to manage large tables, I have found one useful feature introduced in version 5.1

The version 5.1 is very wise in choosing the right indexes. The following query took 6 Minutes 17 seconds to complete on 5.0 version and was almost instantaneous on 5.1 version. The explain plan of that query on 5.0 was...

mysql> explain select * from jaip2 where myd != '2032-00-04' order by cdate desc limit 10\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: jaip2
type: range
possible_keys: myd
key: myd
key_len: 4
ref: NULL
rows: 37025082
Extra: Using where; Using filesort
1 row in set (0.00 sec)


But the same table executes very fast if you have version 5.1

mysql> explain select * from jaip2 where myd != '2032-00-04' order by cdate desc limit 10\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: jaip2
type: index
possible_keys: myd
key: cdate
key_len: 13
ref: NULL
rows: 10
Extra: Using where
1 row in set (0.00 sec)

_____


Since I am using 5.0 version, I had to change the query, so that the same column is used in "where" and "order by". The query is executed fast though it doesn't fetch the records those I want.

mysql> explain select * from jaip2 where cdate < '2032-00-04 00:00:00' order by cdate desc limit 10\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: jaip2
type: range
possible_keys: cdate
key: cdate
key_len: 9
ref: NULL
rows: 37117427
Extra: Using where
1 row in set (0.01 sec)

I guess it's time to upgrade :)