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

Question

Кирилл Петров @Recosh

Программист студент

MySQL

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

Здравствуйте. Существует таблица, в которой есть колонка 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) для каждого дня пока не пробовал, считаю это костылём, который по предчувствию так же будет тормозить.

Вопрос задан более трёх лет назад
552 просмотра

Комментировать

Подписаться 1 Простой Комментировать

Решения вопроса 1

1 комментарий

Кирилл Петров @Recosh Автор вопроса

Сделал через 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;
    }

Написано более трёх лет назад

Пригласить эксперта

Ответы на вопрос 2

Комментировать

3 комментария

Кирилл Петров @Recosh Автор вопроса

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

Написано более трёх лет назад
Александр Аксентьев @Sanasol

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

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

Написано более трёх лет назад
Melkij @Melkij

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

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

Написано более трёх лет назад

Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации

Похожие вопросы

MySQL

+1 ещё

Простой
Как извлечь топ 15 очков из таблицы чтобы игроки не дублировались?
- 1 подписчик
- 22 апр.
- 112 просмотров
1

ответ
MySQL

Простой
Как сохранить mysql базу?
- 1 подписчик
- 21 апр.
- 91 просмотр
1

ответ
PHP

+1 ещё

Простой
Как исправить ошибку Uncaught Error: Call to undefined function mysql_real_escape_string?
- 1 подписчик
- 21 апр.
- 103 просмотра
3

ответа
PHP

+2 ещё

Средний
Ошибка при регистрации PHP, в Unity, что делать?
- 1 подписчик
- 20 апр.
- 98 просмотров
1

ответ
MySQL

Простой
Объясните CASE WHEN THEN?
- 1 подписчик
- 20 апр.
- 107 просмотров
2

ответа
MySQL

Простой
Как обновить определенные значения в базе, в зависимости от значения переменной?
- 1 подписчик
- 20 апр.
- 42 просмотра
1

ответ
PHP

+2 ещё

Простой
Как в Drupal 10 массово проставить noindex для >1000 страниц?
- 1 подписчик
- 18 апр.
- 53 просмотра
2

ответа
MySQL

+1 ещё

Средний
Как восстановить базу данных mysql, если служба mysql не запускается на windows?
- 1 подписчик
- 18 апр.
- 111 просмотров
1

ответ
WordPress

+1 ещё

Простой
Как импортировать большую базу данных в Wordpress?
- 1 подписчик
- 18 апр.
- 68 просмотров
3

ответа
MySQL

+1 ещё

Средний
MySQL ошибка InnoDB: Attempted to open a previously opened tablespace. Куда копать?
- 1 подписчик
- 15 апр.
- 58 просмотров
1

ответ
Показать ещё Загружается…

Программист C++ Builder / базы данных MySQL

RU Electronics • Москва

от 180 000 до 200 000 ₽

Инженер технической поддержки с английским языком и знанием PHP/MySQL

IT-Aces

от 100 000 до 150 000 ₽

PHP FullStack Developer (Middle+)

ГК «Талант» • Сочи

от 100 000 до 200 000 ₽

Доработка индикатора

25 апр. 2024, в 14:05

2000 руб./за проект

Разработать дизайн карточки для маркетплейсов

25 апр. 2024, в 14:01

3000 руб./за проект

Веб-приложения "Афиша" и "Бронирование":

25 апр. 2024, в 13:59

20000 руб./за проект

Answer 1 · 2018-06-27 14:50:18

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

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

Answer 2 · 2018-06-27 14:46:45

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

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

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

Answer 3 · 2018-06-27 14:43:59

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

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

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

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

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

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

Войдите, чтобы написать ответ

Минуточку внимания

Войдите на сайт