Как создать индекс в части даты поля DATETIME в MySql

Что говорит «объяснение»? (запустите EXPLAIN SELECT * FROM transactionlist, где date (TranDateTime) = '2008-08-17')

Если он не использует ваш индекс из-за функции date (), запрос диапазона должен выполняться быстро:

ВЫБЕРИТЕ * ИЗ списка транзакций, где TranDateTime> = '2008-08-17' И TranDateTime <'2008-08-18'

Я не знаю о специфике mySql, но что плохого в том, чтобы просто полностью проиндексировать поле даты?

Тогда просто ищите:

 select * from translist 
     where TranDateTime > '2008-08-16 23:59:59'
        and TranDateTime < '2008-08-18 00:00:00'

Если индексы представляют собой b-деревья или что-то еще разумное, их следует быстро найти.

Если я правильно помню, это запустит сканирование всей таблицы, потому что вы передаете столбец через функцию. MySQL будет послушно запускать функцию для каждого столбца, минуя индекс, поскольку оптимизатор запросов действительно не может знать результаты функции.

Я бы сделал что-то вроде:

SELECT * FROM transactionlist 
WHERE TranDateTime BETWEEN '2008-08-17' AND '2008-08-17 23:59:59.999999';

Это должно дать вам все, что произошло 17 августа 2008 года.

Я не хочу показаться симпатичным, но простым способом было бы добавить новый столбец, который содержал только часть даты и индекс для этого.

Вместо того, чтобы создавать индекс на основе функции (если это возможно даже в mysql), сделайте ваше предложение where для сравнения диапазонов. Что-то вроде:

Where TranDateTime > '2008-08-17 00:00:00' and TranDateTime < '2008-08-17 11:59:59')

Это позволяет БД использовать индекс TranDateTime (он есть, верно?) Для выбора.

Валерий Кравчук в запросе функции для этой самой проблемы на сайте MySQL сказал, что использует этот метод.

«Тем временем вы можете использовать символьные столбцы для хранения значений DATETIME в виде строк с индексированием только первых N символов. При осторожном использовании триггеров в MySQL 5 вы можете создать достаточно надежное решение, основанное на этой идее».

Вы можете довольно легко написать процедуру добавления этого столбца, а затем с помощью триггеров синхронизировать этот столбец. Индекс в этом строковом столбце должен быть довольно быстрым.

Вы не можете создать индекс только для даты. Есть ли причина, по которой вы должны это делать?

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

Я думаю ты найдешь это

SELECT * FROM transactionlist WHERE TranDateTime BETWEEN '2008-08-17' AND '2008-08-18'

Эффективен и делает то, что вы хотите.

Я не знаю о специфике mySQL, но какой вред от простой индексации поля даты целиком?

Если вы используете функциональную магию для * деревьев, хэшей, ... больше нет, потому что для получения значений вы должны вызвать функцию. Но, поскольку вы не знаете результатов, вам нужно провести полное сканирование таблицы.

Добавить нечего.

Может быть, вы имеете в виду что-то вроде вычисленных (вычисленных?) Индексов ... но на сегодняшний день я видел это только в Intersystems Caché. Я не думаю, что это дело в реляционных базах данных (AFAIK).

Хорошим решением, на мой взгляд, является следующее (обновленный пример clintp):

SELECT * FROM translist 
WHERE TranDateTime >= '2008-08-17 00:00:00.0000'
  AND TranDateTime < '2008-08-18 00:00:00.0000'

На мой взгляд, не имеет значения, используете ли вы 00:00:00.0000 или 00:00 (я обычно использовал его в этом формате).

Создайте новые поля только с датами convert(datetime, left(date_field,10)), а затем проиндексируйте их.

Единственное и хорошее решение, которое довольно хорошо работает, - это использовать метку времени в качестве времени, а не datetime. Он хранится как INT и достаточно хорошо проиндексирован. Лично я столкнулся с такой проблемой в таблице транзакций, которая имеет около миллиона записей и сильно замедлилась, наконец, я указал, что это вызвано плохим индексированным полем (datetime). Теперь он работает очень быстро.

datetime LIKE something% тоже не поймает индекс.

Используйте это: ГДЕ datetime_field> = curdate ();
Это поймает индекс
и крышка сегодня: 00: 00: 00 до сегодняшнего дня: 23: 59: 59
Выполнено.

Другой вариант (актуально для версии 5.7.3 и выше) - создать сгенерированный / виртуальный столбец на основе столбца datetime, а затем проиндексировать его.

CREATE TABLE `table` (
`my_datetime` datetime NOT NULL,
`my_date` varchar(12) GENERATED ALWAYS AS (DATE(`my_datetime`)) STORED,
KEY `my_idx` (`my_date`)
) ENGINE=InnoDB;

Если можно изменить таблицу или вы пишете новую, подумайте о том, чтобы сохранить дату и время в отдельных столбцах с соответствующими типами. Вы получаете производительность за счет гораздо меньшего ключевого пространства и меньшего объема памяти (по сравнению со столбцом только для даты, полученным из datetime). Это также делает возможным использование в составных ключах даже перед другими столбцами.

В случае OP:

+-------------------+------------------+------+-----+---------+----------------+
| Field             | Type             | Null | Key | Default | Extra          |
+-------------------+------------------+------+-----+---------+----------------+
| TransactionNumber | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| WagerId           | int(11)          | YES  | MUL | 0       |                |
| TranNum           | int(11)          | YES  | MUL | 0       |                |
| TranDate          | date             | NO   |     | NULL    |                |
| TranTime          | time             | NO   |     | NULL    |                |
| Amount            | double           | YES  |     | 0       |                |
| Action            | smallint(6)      | YES  |     | 0       |                |
| Uid               | int(11)          | YES  |     | 1       |                |
| AuthId            | int(11)          | YES  |     | 1       |                |
+-------------------+------------------+------+-----+---------+----------------+