List:General Discussion« Previous MessageNext Message »
From:xian liu Date:August 24 2007 9:19am
Subject:problem for partition expression ---floor(col_name)
View as plain text  
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!!!
   
   


@@@^_^@@@
       
---------------------------------
雅虎邮箱,以安全著称,是值得信赖的邮箱专家! 
Thread
problem for partition expression ---floor(col_name)xian liu24 Aug