MySQL subpartition by hash

Markus Source
create
  table
    `a`(
      `dt` datetime not null,
      `id` smallint unsigned not null
    ) ENGINE = InnoDB default CHARSET = utf8 collate = utf8_unicode_ci 
    partition by list(id)
    subpartition by hash(YEAR(dt)) subpartitions 2 (
      partition id1 values in (1),
      partition id2 values in (2)
    );

The subpartition by YEAR(dt) doesn't work as expected.

explain select * from a where id = 1 and YEAR(dt) = 2018;

tolds that it is searching for all partitions for id = 1.

> explain select * from a where id = 1 and YEAR(dt) = 2018;
+----+-------------+-------+-----------------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions            | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+-----------------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | a     | id1_id1sp0,id1_id1sp1 | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+-------+-----------------------+------+---------------+------+---------+------+------+----------+-------------+

When I introduce a 3rd column where I save the YEAR seperately and use this column for my subpartition by hash

create
  table
    `b`(
      `dt` datetime not null,
      `id` smallint unsigned not null,
      `yyyy` smallint unsigned not null
    ) ENGINE = InnoDB default CHARSET = utf8 collate = utf8_unicode_ci 
    partition by list(id)
    subpartition by hash(yyyy) subpartitions 2 (
      partition id1 values in (1),
      partition id2 values in (2)
    );

it simply works as expected

> explain select * from b where id = 1 and yyyy = 2018;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | b     | id1_id1sp0 | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

MySQL is only search in one partition of id = 1. I like to prevent to save duplicated informations just in another format. Although it might be faster, because there is no need to extract the year from datetime.

Anyway, is it a bug?

```

select @@version; +-----------+ | @@version | +-----------+ | 5.7.21 | +-----------+ ```

mysqlsqlpartition

Answers

comments powered by Disqus