hi guys,
I'm researching the method of partition when partition key/column is not INT
type.
I create a table t3(a int,b decimal(10,4)) and want to create partition by b
column.
So I use a function floor() for b column, partition type is range:
alter table t3 partition by range(floor(b)) (
partition p0 values less than (6),
partition p1 values less than maxvalue);
I insert 11 rows to t3:
+--------+--------+
| a | b |
+--------+--------+
| 1 | 2.0000 |
| 3 | 4.0000 |
| 0 | 1.0000 |
| 2 | 3.0000 |
| 4 | 5.0000 |
| 5 | 6.0000 |
| 7 | 8.0000 |
| 9 | 8.0000 |
| 6 | 7.0000 |
| 8 | 7.0000 |
| 10 | 9.0000 |
+--------+--------+
If I use "select * from t3 where b=2", can find mysql just scan on partition
via "explain partitions select ..."; but if I use "select * from t3 where b<2",
mysql scan all of the partitions actually! That is, in this case, I can not
take advantage of partition function if I query table with range condition.
Of course, if b is INT type(do not need floor() function), all of those is OK.
Any advice? Really appreciate.
Thanks a lot!!!
@@@^_^@@@
---------------------------------
雅虎邮箱，以安全著称，是值得信赖的邮箱专家！
