Details
-
Bug
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
10.3.9
-
None
-
ubuntu 16.04
Description
I got this table, with partition each day :
CREATE TABLE `ts_value_general_int` (
|
`id_mysql_server` int(11) NOT NULL, |
`id_ts_variable` int(11) NOT NULL, |
`date` datetime NOT NULL,
|
`value` bigint(20) unsigned NOT NULL, |
PRIMARY KEY (`date`,`id_mysql_server`,`id_ts_variable`)
|
) ENGINE=ROCKSDB DEFAULT CHARSET=latin1
|
PARTITION BY RANGE (to_days(`date`))
|
(PARTITION `p737251` VALUES LESS THAN (737251) ENGINE = ROCKSDB, |
PARTITION `p737252` VALUES LESS THAN (737252) ENGINE = ROCKSDB, |
PARTITION `p737253` VALUES LESS THAN (737253) ENGINE = ROCKSDB, |
PARTITION `p737254` VALUES LESS THAN (737254) ENGINE = ROCKSDB, |
PARTITION `p737255` VALUES LESS THAN (737255) ENGINE = ROCKSDB, |
PARTITION `p737257` VALUES LESS THAN (737257) ENGINE = ROCKSDB, |
PARTITION `p737258` VALUES LESS THAN (737258) ENGINE = ROCKSDB, |
PARTITION `p737259` VALUES LESS THAN (737259) ENGINE = ROCKSDB, |
PARTITION `p737260` VALUES LESS THAN (737260) ENGINE = ROCKSDB, |
PARTITION `p737261` VALUES LESS THAN (737261) ENGINE = ROCKSDB, |
PARTITION `p737262` VALUES LESS THAN (737262) ENGINE = ROCKSDB, |
PARTITION `p737263` VALUES LESS THAN (737263) ENGINE = ROCKSDB, |
PARTITION `p737264` VALUES LESS THAN (737264) ENGINE = ROCKSDB, |
PARTITION `p737265` VALUES LESS THAN (737265) ENGINE = ROCKSDB, |
PARTITION `p737266` VALUES LESS THAN (737266) ENGINE = ROCKSDB, |
PARTITION `p737267` VALUES LESS THAN (737267) ENGINE = ROCKSDB, |
PARTITION `p737268` VALUES LESS THAN (737268) ENGINE = ROCKSDB, |
PARTITION `p737269` VALUES LESS THAN (737269) ENGINE = ROCKSDB, |
PARTITION `p737279` VALUES LESS THAN (737279) ENGINE = ROCKSDB, |
PARTITION `p737280` VALUES LESS THAN (737280) ENGINE = ROCKSDB, |
PARTITION `p737285` VALUES LESS THAN (737285) ENGINE = ROCKSDB, |
PARTITION `p737286` VALUES LESS THAN (737286) ENGINE = ROCKSDB, |
PARTITION `p737287` VALUES LESS THAN (737287) ENGINE = ROCKSDB, |
PARTITION `p737288` VALUES LESS THAN (737288) ENGINE = ROCKSDB, |
PARTITION `p737289` VALUES LESS THAN (737289) ENGINE = ROCKSDB, |
PARTITION `p737292` VALUES LESS THAN (737292) ENGINE = ROCKSDB, |
PARTITION `p737293` VALUES LESS THAN (737293) ENGINE = ROCKSDB, |
PARTITION `p737294` VALUES LESS THAN (737294) ENGINE = ROCKSDB, |
PARTITION `p737295` VALUES LESS THAN (737295) ENGINE = ROCKSDB, |
PARTITION `p737296` VALUES LESS THAN (737296) ENGINE = ROCKSDB, |
PARTITION `p737297` VALUES LESS THAN (737297) ENGINE = ROCKSDB, |
PARTITION `p737298` VALUES LESS THAN (737298) ENGINE = ROCKSDB, |
PARTITION `p737299` VALUES LESS THAN (737299) ENGINE = ROCKSDB, |
PARTITION `p737300` VALUES LESS THAN (737300) ENGINE = ROCKSDB, |
PARTITION `p737301` VALUES LESS THAN (737301) ENGINE = ROCKSDB, |
PARTITION `p737306` VALUES LESS THAN (737306) ENGINE = ROCKSDB, |
PARTITION `p737307` VALUES LESS THAN (737307) ENGINE = ROCKSDB, |
PARTITION `p737308` VALUES LESS THAN (737308) ENGINE = ROCKSDB, |
PARTITION `p737309` VALUES LESS THAN (737309) ENGINE = ROCKSDB, |
PARTITION `p737310` VALUES LESS THAN (737310) ENGINE = ROCKSDB, |
PARTITION `p737313` VALUES LESS THAN (737313) ENGINE = ROCKSDB, |
PARTITION `p737314` VALUES LESS THAN (737314) ENGINE = ROCKSDB, |
PARTITION `p737315` VALUES LESS THAN (737315) ENGINE = ROCKSDB, |
PARTITION `p737317` VALUES LESS THAN (737317) ENGINE = ROCKSDB, |
PARTITION `p737318` VALUES LESS THAN (737318) ENGINE = ROCKSDB, |
PARTITION `p737320` VALUES LESS THAN (737320) ENGINE = ROCKSDB, |
PARTITION `p737321` VALUES LESS THAN (737321) ENGINE = ROCKSDB, |
PARTITION `p737322` VALUES LESS THAN (737322) ENGINE = ROCKSDB, |
PARTITION `p737330` VALUES LESS THAN (737330) ENGINE = ROCKSDB, |
PARTITION `p737331` VALUES LESS THAN (737331) ENGINE = ROCKSDB, |
PARTITION `p737332` VALUES LESS THAN (737332) ENGINE = ROCKSDB, |
PARTITION `p737333` VALUES LESS THAN (737333) ENGINE = ROCKSDB, |
PARTITION `p737334` VALUES LESS THAN (737334) ENGINE = ROCKSDB, |
PARTITION `p737335` VALUES LESS THAN (737335) ENGINE = ROCKSDB) |
1 row in set (0.000 sec) |
when i do a explain :
explain partitions(
|
SELECT
|
a.`id_mysql_server`,
|
a.`id_ts_variable`,
|
'' as connection_name, |
a.`date`,
|
a.`value`
|
FROM
|
`ts_value_general_int` a
|
INNER JOIN `ts_date_by_server` b on a.`date` = b.`date`
|
AND a.`id_mysql_server` = b.`id_mysql_server`
|
AND b.`id_ts_file` = 3 |
WHERE
|
id_ts_variable = 484 |
AND a.id_mysql_server = 95 |
AND a.`date` >
|
date_sub(now(), INTERVAL 6 hour) |
);
|
+------+-------------+-------+-----------------+--------+-----------------+-----------------+---------+-------------------------------+------+--------------------------+
|
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-----------------+--------+-----------------+-----------------+---------+-------------------------------+------+--------------------------+
|
| 1 | SIMPLE | b | NULL | range | id_mysql_server | id_mysql_server | 13 | NULL | 229 | Using where; Using index | |
| 1 | SIMPLE | a | p737252,p737335 | eq_ref | PRIMARY | PRIMARY | 13 | pmacontrol.b.date,const,const | 1 | | |
+------+-------------+-------+-----------------+--------+-----------------+-----------------+---------+-------------------------------+------+--------------------------+
|
2 rows in set (0.004 sec) |
 |
|
I don't understand why MySQL check p737252, he should check only the partition p737335. Any idea ?