Как правильно организовать Mysql Partitioning по DAYOFMONTH?

Question

[vasdor]

Здравствуйте!

Имеется таблица:

CREATE TABLE `logs` (
  `INSERT_DATE` datetime DEFAULT NULL,
  `DATA` text NOT NULL,
  KEY `INSERT_DATE` (`INSERT_DATE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (DAYOFMONTH(INSERT_DATE))
(PARTITION p0 VALUES LESS THAN (2) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (3) ENGINE = InnoDB,
 ...
 PARTITION p30 VALUES LESS THAN (32) ENGINE = InnoDB) */

с партициями, сделанными по дням месяца

Запрос

SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE  TABLE_NAME LIKE 'logs';

показывает, что партиции содержат количество записей, соответствующее реальным данным. Т.е. если 20-го числа 17 записей, то и партиция p21 содержит 17 строк. Тут вроде всё хорошо.

Проблема в том, что запрос

EXPLAIN PARTITIONS SELECT * FROM logs WHERE insert_date BETWEEN '2014-10-28' AND '2014-10-29';

показывает в колонке «partitions» все 31 созданных партиций, хотя должен показывать только для соотв. чисел месяца

Вопросы в следующем:
1) Почему Explain так себя ведёт?
2) Возможно ли, что в реальном запросе будут использоваться только нужные партиции?
Заранее спасибо

Answer 1

[Melkij]

Покажите вот так:

EXPLAIN PARTITIONS SELECT * FROM logs WHERE DAYOFMONTH(insert_date) BETWEEN '2014-10-28' AND '2014-10-29';

Comments

[vasdor]

также выводятся все 31 партиция

[vasdor]

id: 1
select_type: SIMPLE
table: logs
partitions: p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14,p15,p16,p17,p18,p19,p20,p21,p22,p23,p24,p25,p26,p27,p28,p29,p30
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 3035
Extra: Using where

[Melkij]

Тьфу, я брежу. Результат запроса-то будет другой - бредовый.

EXPLAIN PARTITIONS SELECT * FROM logs WHERE insert_date in ( '2014-10-28', '2014-10-29')
Так читает только нужные партиции.
Оптимизатор по-видимому тупит и не схлопывает условие диапазона дат, считая что условие insert_date >= '2014-10-28' может выбрать любой день месяца (1 ноября, например) не обращая внимания на конец диапазона.

[vasdor]

Действительно!
Так работает. Спасибо большое!

[vasdor]

Стойте, но это ведь предполагает, что в insert_date будет только date, без времени, а там date_time

[vasdor]

т.е. partitions показываются хорошо, а выборка выдаёт 0 результатов

[Melkij]

Да, я понял. Надо поиграться, но нету сейчас возможности.
Решение в лоб, но надёжно - партицировать по фиктивному отдельному полю для дня месяца (tinyint), заполняемому хоть даже триггером и, соответственно должно нормально работать и day between 28 and 29

[querty]

between попробуйте