Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Incomplete
-
Affects Version/s: 10.0.22
-
Fix Version/s: N/A
-
Component/s: Partitioning
-
Labels:
-
Environment:linux
Description
Hi guys, i'm using partitioning in some tables, and this cause table scan in all partitions, lets check:
CREATE TABLE a (
id decimal(22,1) not null default 0,
primary key (id)
);
CREATE TABLE b(
id decimal(22,1) not null default 0,
id2 int not null default 0,
primary key (id,id2)
)PARTITION BY RANGE (FLOOR(id))
(PARTITION s0 VALUES LESS THAN (100000) ,
PARTITION s1 VALUES LESS THAN (450000) ,
PARTITION s2 VALUES LESS THAN (800000) ,
PARTITION s3 VALUES LESS THAN MAXVALUE)
CREATE TABLE c (
id decimal(22,1) not null default 0,
primary key (id)
);
id int not null default 0,
populate tables with many data (0 - 1000000 rows)
execute:
select a.*,b.* from a,b,c where a.id=b.id and a.id=c.id and b.id=c.id
my explain b don't use index
executing ALTER TABLE b REMOVE PARTITIONING and running query again i have b using primary key
i can provide better test if you need
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
hum, i'm checking again
that's a old table (from mysql 5.1), the FLOOR() is not allowed anymore
i will remove partitioning and partition again with a right partition part
/* Erro SQL (1659): Field 'id' is of a not allowed type for this type of partitioning */
It does not work on MySQL 5.1.61 either.
MySQL [test]> CREATE TABLE b(
-> id decimal(22,1) not null default 0,
-> id2 int not null default 0,
-> primary key (id,id2)
-> )PARTITION BY RANGE (FLOOR(id))
-> (PARTITION s0 VALUES LESS THAN (100000) ,
-> PARTITION s1 VALUES LESS THAN (450000) ,
-> PARTITION s2 VALUES LESS THAN (800000) ,
-> PARTITION s3 VALUES LESS THAN MAXVALUE)
-> ;
ERROR 1491 (HY000): The PARTITION function returns the wrong type
MySQL [test]> select @@version;
+-----------+
| @@version |
+-----------+
| 5.1.61 |
+-----------+
1 row in set (0.01 sec)
roberto spadim,
For now, if possible, fix the description to provide valid create table statements. Thanks.