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 :)
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.