Cómo se crea un índice en la parte fecha del campo DATETIME en MySQL

No quiero sonar lindo, pero una manera simple sería agregar una nueva columna que solo contuviera la parte de fecha e índice en eso.

No se puede crear un índice solo en la parte de fecha. ¿Hay alguna razón para hacerlo?

Incluso si pudiera crear un índice solo en la parte de fecha, el optimizador probablemente no lo usaría para la consulta anterior.

Creo que encontrarás que

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

Es eficiente y hace lo que quieres.

Otra opción (relevante para ver 7.5.3 y superior) es crear una columna generada/virtual basada en la columna datetime, luego indexarla.

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

No conozco los detalles de MySQL, pero ¿cuál es el daño en indexar el campo de fecha en su totalidad?

Entonces solo busca:

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

Si los índices son árboles b u otra cosa que sea razonable, estos deben ser encontrados rápidamente.

Valeriy Kravchuk en una solicitud de característica para este mismo problema en el sitio MySQL dijo que use este método.

"Mientras tanto, puede usar columnas de caracteres para almacenar valores DATETIME como cadenas, con solo los primeros N caracteres indexados. Con un uso cuidadoso de los disparadores en MySQL 5 puede crear una solución razonablemente robusta basada en esta idea."

Podría escribir una rutina bastante fácil de agregar esta columna, y luego con disparadores mantener esta columna sincronizada. El índice en esta columna de cadena debería ser bastante rápida.

La única y buena solución que funciona bastante bien es usar timestamp como tiempo, en lugar de datetime. Se almacena como INT y se indexa lo suficientemente bien. Personalmente me encontré con tal problema en la tabla de transacciones, que tiene alrededor de millones de registros y se ralentizó duro, finalmente señalé que esto causado por mal campo indexado (datetime). Ahora corre muy rápido.

Datetime COMO algo% tampoco captará el índice.

Use esto: DONDE datetime_field > = curdate();
Que atrapará el índice,
y cubrir hoy: 00: 00: 00 hasta hoy:23:59:59
Terminado.

¿Qué dice 'explicar'? (run EXPLAIN SELECT * FROM transactionlist where date (TranDateTime) = '2008-08-17')

Si no está usando su índice debido a la función date (), una consulta de rango debería ejecutarse rápidamente:

SELECCIONE * DE LA lista DE transacciones donde TranDateTime > = ' 2008-08-17 'Y TranDateTime

En lugar de hacer un índice basado en una función (si eso es incluso posible en mysql) haga que su cláusula where haga una comparación de rangos. Algo como:

Donde TranDateTime > ' 2008-08-17 00:00:00' y TranDateTime

Esto permite que la base de datos use el índice en TranDateTime (hay uno, ¿verdad?) para hacer la selección.

Crea nuevos campos con solo las fechas convert(datetime, left(date_field,10)) y luego indexa eso.

No conozco los detalles de MySQL, pero ¿cuál es el daño en indexar el campo de fecha en su totalidad?

Si utiliza magia funcional para * árboles, hashes, ... se ha ido, porque para obtener valores debe llamar a la función. Pero, debido a que usted no sabe los resultados por delante, usted tiene que hacer un análisis completo de la tabla.

No Hay nada que añadir.

Tal vez quieres decir algo como computado (calculado?) índices... pero hasta la fecha, solo he visto esto en Intersystems Caché. No creo que haya un caso en las bases de datos relacionales (AFAIK).

Una buena solución, en mi opinión, es la siguiente (ejemplo actualizado de clintp):

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

Si usas 00:00:00.0000 o 00:00 en mi opinión no hace ninguna diferencia (generalmente lo he usado en este formato).

¿Por qué nadie sugirió usar LIKE? ¿No hace eso el trabajo también? ¿Será tan rápido como entre ambos?

SELECT * FROM transactionlist where TranDateTime LIKE '2008-08-17%'