Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Incomplete
-
10.1.19, 10.1.20, 10.1.21
-
Windows 7
Description
After creating an Aria partitioned table with the following definition:
CREATE TABLE scada_data_processing.`t0_DISPOIN_imported_data`
|
(
|
`id_scada_data` INT unsigned NOT NULL AUTO_INCREMENT,
|
`MachineID` VARCHAR(25), |
`SCADA_Code` INT NOT NULL DEFAULT '-2', |
`Description` VARCHAR(255) CHARACTER SET 'latin2' DEFAULT NULL , |
`DateTimeON` DATETIME,
|
`DateTimeOFF` DATETIME,
|
`Time` TIME DEFAULT NULL,
|
`Comment` VARCHAR(255) DEFAULT NULL, |
`IsAlarm` VARCHAR(255) DEFAULT NULL, |
`Open` VARCHAR(255) DEFAULT NULL, |
PRIMARY KEY(`id_scada_data`, `SCADA_Code`),
|
INDEX (`MachineID`),
|
INDEX(`SCADA_Code`),
|
INDEX(`DateTimeON`),
|
INDEX(`DateTimeOFF`)
|
)
|
ENGINE=Aria
|
CHARSET='utf8mb4' |
PARTITION BY RANGE (`SCADA_Code`)
|
(
|
PARTITION p0_DismissedCodes VALUES LESS THAN (0), |
PARTITION p1_DownTime VALUES LESS THAN (4), |
PARTITION p2_Operative VALUES LESS THAN (6), |
PARTITION p3_DownTime VALUES LESS THAN (11), |
PARTITION p4_SCADAData VALUES LESS THAN (2509), |
PARTITION p5_NoCommunication VALUES LESS THAN (2510), |
PARTITION p6_SCADAData VALUES LESS THAN MAXVALUE);
|
When I try to do explicit partition selection (Theoretically possible as indicates MariaDB documentation), the OPTIMIZER tells me when I execute the following code:
EXPLAIN PARTITIONS SELECT
|
*
|
FROM
|
scada_data_processing.`t0_DISPOIN_imported_data`
|
PARTITION (p4_SCADAData, p6_SCADAData);
|
That:
table | partitions | type |
---|---|---|
t0_DISPOIN_imported_data | p4_SCADAData,p6_SCADAData | ALL |
Therefore, theorically only desired partitions are selected.
However, after performing the same INSERT INTO ... SELECT operation with InnoDB, MyISAM and Aria Engines (All engines perfectly configured), it took:
- Nearly 13 [min] for Aria Engine.
- 6 [min] to InnoDB Engine.
- And 7,5 [min] to MyISAM.
To perform the same statement.
Maybe this performance decrease could be due to the fact that when pruning is performed on a partitioned MyISAM table (or Aria), all partitions are opened, whether or not they are examined, due to the design of the MyISAM storage engine.
For Aria Engine It 13 [min] to perform the operation either using ROW_FORMAT = PAGE or ROW_FORMAT = DYNAMIC.
Thank you for your help.
Kind regards,
Juan