español - ¿Cómo se crea un índice en la parte de la fecha del campo DATETIME en MySql?
mysql wikipedia (13)
¿Cómo creo un índice en la parte de la fecha del campo DATETIME?
mysql> SHOW COLUMNS FROM transactionlist;
+-------------------+------------------+------+-----+---------+----------------+
| 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 | |
| TranDateTime | datetime | NO | | NULL | |
| Amount | double | YES | | 0 | |
| Action | smallint(6) | YES | | 0 | |
| Uid | int(11) | YES | | 1 | |
| AuthId | int(11) | YES | | 1 | |
+-------------------+------------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)
TranDateTime se usa para guardar la fecha y hora de una transacción a medida que sucede
Mi tabla tiene más de 1,000,000 de registros y la declaración
SELECT * FROM transactionlist where date(TranDateTime) = ''2008-08-17''
toma mucho tiempo.
EDITAR:
Echa un vistazo a esta entrada de blog sobre " Por qué se puede y se debe evitar DATETIME de MySQL "
¿Por qué nadie sugirió usar LIKE? ¿Eso no hace el trabajo también? ¿Será tan rápido como BETWEEN?
SELECT * FROM transactionlist where TranDateTime LIKE ''2008-08-17%''
¿Qué dice ''explicar''? (ejecuta EXPLAIN SELECT * FROM transactionlist donde date (TranDateTime) = ''2008-08-17'')
Si no utiliza su índice debido a la función de fecha (), una consulta de rango debe ejecutarse rápidamente:
SELECT * FROM transactionlist donde TranDateTime> = ''2008-08-17'' AND TranDateTime <''2008-08-18''
Cree un nuevo campo con las fechas convert(datetime, left(date_field,10)) y luego indexe eso.
En lugar de hacer un índice basado en una función (si eso es posible incluso en mysql) haga que su cláusula where haga una comparación de rango. Algo como:
Donde TranDateTime> ''2008-08-17 00:00:00'' y TranDateTime <''2008-08-17 11:59:59'')
Esto permite que DB use el índice en TranDateTime (hay uno, ¿verdad?) Para hacer la selección.
La única y buena solución que funciona bastante bien es usar la marca de tiempo como hora, en lugar de fecha y hora. Se almacena como INT y se indexa lo suficientemente bien. Personalmente me encontré con ese problema en la tabla de transacciones, que tiene alrededor de un millón de registros y se ralentizó mucho, finalmente señalé que esto se debe a un campo indexado incorrecto (datetime). Ahora funciona muy rápido.
No conozco los detalles de mySQL, pero ¿cuál es el daño al solo indexar el campo de fecha en su totalidad?
Si usa magia funcional para * trees, hashes, ... se ha ido, porque para obtener valores debe llamar a la función. Pero, debido a que no conoce los resultados futuros, debe hacer un análisis completo de la tabla.
No hay nada que agregar.
Tal vez te refieres a algo así como índices calculados (calculados) ... 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 de clintp actualizado):
SELECT * FROM translist
WHERE TranDateTime >= ''2008-08-17 00:00:00.0000''
AND TranDateTime < ''2008-08-18 00:00:00.0000''
Ya sea que use 00:00:00.0000 o 00:00 en mi opinión no hace diferencia (generalmente lo he usado en este formato).
No pretendo sonar lindo, pero una manera simple sería agregar una nueva columna que solo contenga la parte de la fecha y el índice sobre eso.
No puede crear un índice solo en la parte de la fecha. ¿Hay alguna razón por la que tienes que hacerlo?
Incluso si pudieras crear un índice solo en la parte de la fecha, el optimizador probablemente aún no la usaría para la consulta anterior.
Creo que encontrarás eso
SELECT * FROM transactionlist WHERE TranDateTime BETWEEN ''2008-08-17'' AND ''2008-08-18''
Es eficiente y hace lo que quiere.
No sé acerca de los detalles de mySql, pero ¿cuál es el daño en solo indexar el campo de fecha en su totalidad?
Luego 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 b-trees o cualquier otra cosa que sea razonable, deberían encontrarse rápidamente.
Otra opción (relevante para la versión 7.5.3 y superior) es crear una columna generada / virtual basada en la columna de fecha y hora, y 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;
Si mal no recuerdo, eso ejecutará un escaneo completo de la tabla porque está pasando la columna a través de una función. MySQL ejecutará obedientemente la función para cada columna, omitiendo el índice, ya que el optimizador de consultas no puede conocer realmente los resultados de la función.
Lo que yo haría es algo así como:
SELECT * FROM transactionlist
WHERE TranDateTime BETWEEN ''2008-08-17 00:00:00'' AND ''2008-08-18 23:59:59'';
Eso debería darle todo lo que sucedió el 2008-08-17, y todo lo que sucedió exactamente el 2008-08-18 00:00:00. Si eso es un problema, puede cambiar el segundo término a ''2008-08-17 23:59:59'' y obtener 2008-08-17.
Valeriy Kravchuk en una solicitud de función para este mismo problema en el sitio de MySQL dice que utiliza este método.
"Mientras tanto, puede usar columnas de caracteres para almacenar los valores DATETIME como cadenas, con solo los primeros N caracteres indexados. Con un uso cuidadoso de los desencadenadores en MySQL 5 puede crear una solución razonablemente robusta basada en esta idea".
Puede escribir una rutina bastante fácil para agregar esta columna, y luego con activadores mantener esta columna sincronizada. El índice en esta columna de cadena debe ser bastante rápido.
datetime LIKE algo% tampoco captará el índice.
Use esto: WHERE datetime_field> = curdate ();
Eso atrapará el índice,
y cubra hoy: 00: 00: 00 hasta hoy: 23: 59: 59
Hecho.