Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-17355

explain partition

    XMLWordPrintable

Details

    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 ?

      Attachments

        Activity

          People

            greenman Ian Gilfillan
            Aurelien_LEQUOY Aurélien LEQUOY
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.