[MDEV-8662] partitioning forcing a table scan in each partition Created: 2015-08-21  Updated: 2015-09-20  Resolved: 2015-09-20

Status: Closed
Project: MariaDB Server
Component/s: Partitioning
Affects Version/s: 10.0.22
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: roberto spadim Assignee: Unassigned
Resolution: Incomplete Votes: 0
Labels: need_feedback
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

 Comments   
Comment by Elena Stepanova [ 2015-08-21 ]

rspadim,

i can provide better test if you need

For now, if possible, fix the description to provide valid create table statements. Thanks.

Comment by roberto spadim [ 2015-08-21 ]

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 */

Comment by Elena Stepanova [ 2015-09-20 ]

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)

Generated at Thu Feb 08 07:28:50 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.