[SOLUCIONADO] [Aporte] Hacer query por date en columna tipo datetime (indexada)

[abimaelrc]

Saludos,

Desde hace un tiempo en unas estadísticas, la base de datos creció bastante y me di cuenta que la función DATE verifica desde el inicio de la tabla a pesar de que está indexado el campo y la tabla en la base de datos está bastante grande, eso ocasionaba que los resultados se tardaran minutos. Buscando en internet encontré una buena solución (lamentablemente no me acuerdo en donde fue para postear el enlace, ya que fue hace mucho tiempo) que resuelve el problema. Con usar BETWEEN se puede ver lo que uno desea, es decir

Código MySQL:

SELECT * FROM tabla WHERE column_datetime BETWEEN "2013-04-01" AND "2013-04-10 23:59:59"
 

No es necesario colocar la hora 00:00:00 porque ya el primero lo hace indirectamente.

Si hay otra alternativa espero sugerencia, pero con esto me ha resultado.

[leonardo_josue]

Hola abimaelrc:

Me parece muy interesante este dato, pero me gustaría que nos colocaras un ejemplo en cuando a las diferencias de tiempos que comentas... es decir, que nos digas de cuantos registros estamos hablando y cuál es la diferencia en tiempos de las consultas. Se me ocurren otra alternativa utilizando DATE_FORMAT, igual también podrías probarlas y comentarnos los resultados:

Código MySQL:

SELECT * FROM tabla WHERE 
column_datetime BETWEEN '2013-04-01' AND '2013-04-10 23:59:59'
 
SELECT * FROM tabla WHERE 
DATE(column_datetime) = '2013-04-01'
 
SELECT * FROM tabla WHERE 
DATE_FORMAT(column_datetime, '%Y-%m-%d') = '2013-04-01'
 

Saludos
Leo.

[gnzsoloyo]

Esto es algo que alguna vez alguien consultó, y que tuve que investigar un poco para encontrarlo.
Resultó ser producto de la corrección de un bug, que se menciona en el manual de referencia, que existía en la versión 4.0.x: Al comparar dos campos de fecha de diferente longitud "cortaba" las horas y devolvía un DATE, con todas las malas consecuencias imaginables.

A partir de la versión 5.0.x, MySQL realiza una conversión implícita cuando compara fechas, transformando todas al tipo de dato de más alto nivel (DATETIME), y como no existen horas referidas en un DATE, lo que hace es poner las horas a cero.

Francamente no creo que haya diferencias performánticas notorias, porque en ambos casos se están generando conversiones, unas explícitas y otras implícitas. Si hay alguna, debe manifestarse con consultas de millones de registros.

[abimaelrc]

A eso es que voy. Tengo base de datos con un poco más de 2 millones de registro y una consulta de un día en específico se tarda usando DATE o DATE_FORMAT como si la tabla no estuviera indexada. Es decir que hace la búsqueda desde el principio. Pero si lo hago como lo indico es menos de 2 segundos devuelve los resultados.

En unos momentos posteo los resultados de consultas hechas a esa base de datos para que veas los tiempos.

[gnzsoloyo]

Es un problema de decisión de selección de índice que realiza el parser.
Cuando usas DATE() o STR_TO_DATE, en realidad estás convirtiendo algo que tiene el mismo formato de la clave de índice, por algo diferente. En ese caso no detecta igualdad y lo descarta (Oracle hace algo parecido en ciertos casos).
En cambio, cuando realizas la consulta así:

Código MySQL:

SELECT * 
FROM tabla 
WHERE column_datetime BETWEEN "2013-04-01" AND "2013-04-10 23:59:59"
 

antes de verificar si el indice es aplicable tiene que realizar la conversión y en ese contexto, el dato es del mimo tipo y longitud... y el indice aplica.
Para estar más seguros, habría que probar qué pasa al usar:

Código MySQL:

SELECT * 
FROM tabla 
WHERE column_datetime BETWEEN STR_TO_DATE('2013-04-01 00:00:00') AND STR_TO_DATE('2013-04-10 23:59:59);
 

Lo que sí es seguro es que al usar DATE_FORMAT() sobre el campo indexado, el índice se descarte, precisamente porque se pierde la relación entre el dato y el índice.

[abimaelrc]

Código MySQL:

mysql> SELECT COUNT(*) FROM tabla;
+----------+
| COUNT(*) |
+----------+
|  1303633 |
+----------+
1 row in set (1.40 sec)
 
mysql> SELECT * FROM tabla WHERE DATE(created_datetime) = "2013-04-16" limit 2;
2 rows in set (5.04 sec)
 
mysql> SELECT * FROM tabla WHERE DATE_FORMAT(created_datetime, "%Y-%m-%d") = "2013-04-16" limit 2;
2 rows in set (5.27 sec)
 
mysql> SELECT * FROM tabla WHERE created_datetime BETWEEN "2013-04-16" AND "2013-04-16 23:59:59" limit 2;
2 rows in set (0.00 sec)
 

Cada día se ingresa sobre 6 mil casos por lo que si pido traer los 6 mil se tardaría un poco más la consulta. Pero ahí está la idea.

[abimaelrc]

Según lo que me indicaste gnzsoloyo, esto es lo que me retornó

Código MySQL:

mysql> SELECT * FROM tabla WHERE created_datetime BETWEEN STR_TO_DATE("2013-04-16 00:00:00", "%Y-%m-%d %H:%i:%s") AND STR_TO_DATE("2013-04-16 23:59:59", "%Y-%m-%d %H:%i:%s") LIMIT 2;
2 rows in set (0.00 sec)
 

Muy interesante, gracias por el dato gnzsoloyo. Ya veo que está pasando, bueno leve idea

[gnzsoloyo]

No veo que hayas probado la que te pasé...

Las dos primeras que pruebas forzosamente vana descartar el índice, si el mismo está basado en un campo DATETIME. La primera, porque lo cortas a DATE, y por tanto no le sirve para buscar claves. la segunda porque lo conviertes en texto, y tiene que hacer las búsquedas en modo binario sobre cadenas, que no tienen relación con la clave del indice.
Recuerda que los campos de tipo fecha se almacenan en la base como numeros binarios, no como fechas.

[abimaelrc]

Ok tal vez comentaste, mientras editaba, verifica si es correcto lo que copié.

[gnzsoloyo]

Perfecto, abimaelrc. Por lo que se alcanza a ver el resultado es lo que me imaginaba que podía ocurrir. Yo no lo había probado comparativamente más que en ciertas ocasiones, pero tenía la impresión de que podía resultar bien.
perdón el error del post, eso si. Se me olvidó completar correctamente la función

[leonardo_josue]

Excelente!!!

Para tomarse en cuenta de ahora en adelante.

Saludos
Leo.