Foros del Web » Programación para mayores de 30 ;) » Bases de Datos General » Mysql »

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

Estas en el tema de [Aporte] Hacer query por date en columna tipo datetime (indexada) en el foro de Mysql en Foros del Web. 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 ...
  #1 (permalink)  
Antiguo 16/04/2013, 12:10
Avatar de abimaelrc
Colaborador
 
Fecha de Ingreso: mayo-2009
Ubicación: En el planeta de Puerto Rico
Mensajes: 14.734
Antigüedad: 10 años, 4 meses
Puntos: 1516
[Aporte] Hacer query por date en columna tipo datetime (indexada)

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:
Ver original
  1. 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.
__________________
Verifica antes de preguntar.
Los verdaderos amigos se hieren con la verdad, para no perderlos con la mentira. - Eugenio Maria de Hostos
  #2 (permalink)  
Antiguo 16/04/2013, 14:09
Colaborador
 
Fecha de Ingreso: enero-2007
Ubicación: México
Mensajes: 2.097
Antigüedad: 12 años, 9 meses
Puntos: 447
Respuesta: [Aporte] Hacer query por date en columna tipo datetime (indexada)

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:
Ver original
  1. SELECT * FROM tabla WHERE
  2. column_datetime BETWEEN '2013-04-01' AND '2013-04-10 23:59:59'
  3.  
  4. SELECT * FROM tabla WHERE
  5. DATE(column_datetime) = '2013-04-01'
  6.  
  7. SELECT * FROM tabla WHERE
  8. DATE_FORMAT(column_datetime, '%Y-%m-%d') = '2013-04-01'
Saludos
Leo.

Última edición por gnzsoloyo; 16/04/2013 a las 20:10
  #3 (permalink)  
Antiguo 16/04/2013, 20:16
Avatar de gnzsoloyo
Moderador criollo
 
Fecha de Ingreso: noviembre-2007
Ubicación: Actualmente en Buenos Aires (el enemigo ancestral)
Mensajes: 23.318
Antigüedad: 11 años, 10 meses
Puntos: 2653
Respuesta: [Aporte] Hacer query por date en columna tipo datetime (indexada)

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.
__________________
¿A quién le enseñan sus aciertos?, si yo aprendo de mis errores constantemente...
"El problema es la interfase silla-teclado." (Gillermo Luque)
  #4 (permalink)  
Antiguo 17/04/2013, 11:36
Avatar de abimaelrc
Colaborador
 
Fecha de Ingreso: mayo-2009
Ubicación: En el planeta de Puerto Rico
Mensajes: 14.734
Antigüedad: 10 años, 4 meses
Puntos: 1516
Respuesta: [Aporte] Hacer query por date en columna tipo datetime (indexada)

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.
__________________
Verifica antes de preguntar.
Los verdaderos amigos se hieren con la verdad, para no perderlos con la mentira. - Eugenio Maria de Hostos
  #5 (permalink)  
Antiguo 17/04/2013, 11:51
Avatar de gnzsoloyo
Moderador criollo
 
Fecha de Ingreso: noviembre-2007
Ubicación: Actualmente en Buenos Aires (el enemigo ancestral)
Mensajes: 23.318
Antigüedad: 11 años, 10 meses
Puntos: 2653
Respuesta: [Aporte] Hacer query por date en columna tipo datetime (indexada)

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:
Ver original
  1. FROM tabla
  2. 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:
Ver original
  1. FROM tabla
  2. 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.
__________________
¿A quién le enseñan sus aciertos?, si yo aprendo de mis errores constantemente...
"El problema es la interfase silla-teclado." (Gillermo Luque)

Última edición por gnzsoloyo; 17/04/2013 a las 11:57
  #6 (permalink)  
Antiguo 17/04/2013, 11:58
Avatar de abimaelrc
Colaborador
 
Fecha de Ingreso: mayo-2009
Ubicación: En el planeta de Puerto Rico
Mensajes: 14.734
Antigüedad: 10 años, 4 meses
Puntos: 1516
Respuesta: [Aporte] Hacer query por date en columna tipo datetime (indexada)

Código MySQL:
Ver original
  1. mysql> SELECT COUNT(*) FROM tabla;
  2. +----------+
  3. | COUNT(*) |
  4. +----------+
  5. |  1303633 |
  6. +----------+
  7. 1 row in set (1.40 sec)
  8.  
  9. mysql> SELECT * FROM tabla WHERE DATE(created_datetime) = "2013-04-16" limit 2;
  10. 2 rows in set (5.04 sec)
  11.  
  12. mysql> SELECT * FROM tabla WHERE DATE_FORMAT(created_datetime, "%Y-%m-%d") = "2013-04-16" limit 2;
  13. 2 rows in set (5.27 sec)
  14.  
  15. mysql> SELECT * FROM tabla WHERE created_datetime BETWEEN "2013-04-16" AND "2013-04-16 23:59:59" limit 2;
  16. 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.
__________________
Verifica antes de preguntar.
Los verdaderos amigos se hieren con la verdad, para no perderlos con la mentira. - Eugenio Maria de Hostos
  #7 (permalink)  
Antiguo 17/04/2013, 12:12
Avatar de abimaelrc
Colaborador
 
Fecha de Ingreso: mayo-2009
Ubicación: En el planeta de Puerto Rico
Mensajes: 14.734
Antigüedad: 10 años, 4 meses
Puntos: 1516
Respuesta: [Aporte] Hacer query por date en columna tipo datetime (indexada)

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

Código MySQL:
Ver original
  1. 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. 2 rows in set (0.00 sec)

Muy interesante, gracias por el dato gnzsoloyo. Ya veo que está pasando, bueno leve idea
__________________
Verifica antes de preguntar.
Los verdaderos amigos se hieren con la verdad, para no perderlos con la mentira. - Eugenio Maria de Hostos
  #8 (permalink)  
Antiguo 17/04/2013, 12:14
Avatar de gnzsoloyo
Moderador criollo
 
Fecha de Ingreso: noviembre-2007
Ubicación: Actualmente en Buenos Aires (el enemigo ancestral)
Mensajes: 23.318
Antigüedad: 11 años, 10 meses
Puntos: 2653
Respuesta: [Aporte] Hacer query por date en columna tipo datetime (indexada)

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.
__________________
¿A quién le enseñan sus aciertos?, si yo aprendo de mis errores constantemente...
"El problema es la interfase silla-teclado." (Gillermo Luque)
  #9 (permalink)  
Antiguo 17/04/2013, 12:22
Avatar de abimaelrc
Colaborador
 
Fecha de Ingreso: mayo-2009
Ubicación: En el planeta de Puerto Rico
Mensajes: 14.734
Antigüedad: 10 años, 4 meses
Puntos: 1516
Respuesta: [Aporte] Hacer query por date en columna tipo datetime (indexada)

Ok tal vez comentaste, mientras editaba, verifica si es correcto lo que copié.
__________________
Verifica antes de preguntar.
Los verdaderos amigos se hieren con la verdad, para no perderlos con la mentira. - Eugenio Maria de Hostos
  #10 (permalink)  
Antiguo 17/04/2013, 13:14
Avatar de gnzsoloyo
Moderador criollo
 
Fecha de Ingreso: noviembre-2007
Ubicación: Actualmente en Buenos Aires (el enemigo ancestral)
Mensajes: 23.318
Antigüedad: 11 años, 10 meses
Puntos: 2653
Respuesta: [Aporte] Hacer query por date en columna tipo datetime (indexada)

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
__________________
¿A quién le enseñan sus aciertos?, si yo aprendo de mis errores constantemente...
"El problema es la interfase silla-teclado." (Gillermo Luque)
  #11 (permalink)  
Antiguo 17/04/2013, 14:02
Colaborador
 
Fecha de Ingreso: enero-2007
Ubicación: México
Mensajes: 2.097
Antigüedad: 12 años, 9 meses
Puntos: 447
Respuesta: [Aporte] Hacer query por date en columna tipo datetime (indexada)

Excelente!!!

Para tomarse en cuenta de ahora en adelante.

Saludos
Leo.

Etiquetas: campo, columna, datetime, query, select, tabla, tipo
Atención: Estás leyendo un tema que no tiene actividad desde hace más de 6 MESES, te recomendamos abrir un Nuevo tema en lugar de responder al actual.
Respuesta

SíEste tema le ha gustado a 1 personas




La zona horaria es GMT -6. Ahora son las 04:47.