Виртуальная колонка в MySQL или как правильно работать с датами в MySQL?

Question

[Кирилл Петров]

Здравствуйте. Существует таблица, в которой есть колонка datetime, где содержится дата и время. Поле проиндексировано.

И допустим хочу достать данные за определённые числа, и сконструировал запрос с таким условием

WHERE DATE_FORMAT(date, "%Y-%m-%d") IN ("2018-06-19","2018-06-20","2018-06-26")

И всё повисло... База на продакшене, добавлять новое поле с типом DATE довольно долго, данных очень много. Подскажите как правильно построить запрос.

Вариант с диапазонами (2018-06-19 00:00:00 - 2018-06-19 23:59:59) для каждого дня пока не пробовал, считаю это костылём, который по предчувствию так же будет тормозить.

Answer 1

[Melkij]

Формат условия "функция(поле_таблицы) оператор" в принципе не может использовать индекс по этому полю. Если нет других селективных индексов - гарантированный fullscan.
Формат условия "поле_таблицы between ? and ?" - может использовать индекс.

Выводы?
3 диапазона через or или, возможно, эффективнее будет 3 union all в каждом по одному диапазону. Не помню насколько адекватно нынче mysql ведёт себя с or.

Comments

[Кирилл Петров]

Сделал через 3 диапазона OR. Работает шустро
Код для генерации условия в базу

function build_between_sql($field,$array){
      $stings_arr = array();
      foreach ($array as $value) {
        $stings_arr[] = $field . ' BETWEEN ' . $this->db->escape($value['ot']) . ' AND ' . $this->db->escape($value['do']);
      }

      $where = '('.join(' OR ', $stings_arr).')';

      return $where;
    }

Answer 2

[Антон Антон]

between начало дня and конец дня и union all на каждую дату.

Вариант с диапазонами (2018-06-19 00:00:00 - 2018-06-19 23:59:59) для каждого дня пока не пробовал, считаю это костылём, который по предчувствию так же будет тормозить.

нет, не будет.

Answer 3

[Александр Аксентьев]

Как минимум можно убрать DATE_FORMAT(хотя может это только для date подойдёт, а не datetime), в остальном никаких оптимизаций не вижу возможных для выборки случайных дат пачкой.

Индексируйте как-нибудь еще, либо ограничивайте выборки на уровне интерфейса(только периодами).
Может попробовать в элестик/sphinx засунуть.

База на продакшене, добавлять новое поле с типом DATE довольно долго, данных очень много

mysql 8+ умеет добавлять быстро новые столбцы.

Только толку добавлять date, если с datetime не можете разобраться, шило на мыло поменяется.

Comments

[Кирилл Петров]

Есть другая таблица с колонкой с типом DATE, и по ней всё работает быстро, ну естественно без date_format

[Александр Аксентьев]

Кирилл Петров, на datetime без date_format не работает?

Не пробовал в mysql, в postgresql вроде умеет как угодно с датами и их сравнением.

[Melkij]

в postgresql вроде умеет как угодно с датами и их сравнением.

Мой ответ, кстати, для postgresql так же актуален. За одним нюансом - в postgresql можно сделать индекс по immutable функции - если дополнительный индекс обходится дешевле чем переписать запрос. Но условия вида "функция(поле_таблички) оператор" всё равно должны настораживать.
К слову, аналогично должны настораживать явные приведения типа. field::date = ? - индекс по field использоваться уже не может.