porque este indice no funciona??

[cmunozp]

Hola estimados estoy comprobando la optimizacion de mis consultas y me encuentro con el siguiente indice que no funciona:

la consulta es la siguiente:

existe un indice por que campo fecha_ejecucion

SELECT a.id_proyecto, a.id_actividad, a.id, a.nombre, a.fecha_ejecucion, a.fecha_ingreso, a.id_usuario
FROM actividades a
WHERE a.fecha_ejecucion >= '2007-01-01'
AND a.fecha_ejecucion <= '2007-12-01'

si aplico explain a la consulta esto es lo que aparece:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE a ALL fecha_ejecucion NULL NULL NULL 8639 Using where; Using filesort


que segun yo no esta tomando el indice para nada

como puedo hacer que el indice funciona???


saludos

[gnzsoloyo]

Es decisión del parser de optimización. Resuelve qué es lo que le llevaría menos tiempo.

Buscar un ">=", "<=", ">" o "<" cuando no se trata de indices primarios, se realiza por medio de un barrido de tabla directo, por cuanto estadísticamente se sabe que la cantidad de registros a leer está siempre en el orden de n/2, siendo n = cantidad de registros de la tabla, lo que en defintiva es el mismo resultado que le daría en la lectura sin índices.

Si lo quieres forzar a usarlo, tienes que indicarle FORCE INDEX indice, sino va a resolver pos la lógica de los algoritmos directamente.

[jurena]

Sin embargo, si usara para el caso de >= AND <= la búsqueda por rango con BETWEEN AND, por ej., WHERE campofecha BETWEEN '2007-04-03' AND '2008-03-02' y le hace el explain observaría que el índice sí se usa.
Gnzsoloyo,
quizás no sea este el post indicado, pero quisiera saber si existe alguna información técnica de nivel básico y aplicado a bases de datos, en particular MySQL, que nos ayude a entender mejor el funcionamiento del programa, y en la que se aporten datos como el que Vd. nos ofrece sobre la estadística de esa consulta sin índice, más allá de lo que dice el manual de referencia y sin tener que matricularme en una carrera técnica para hacer álgebra relacional (soy un hombre de letras), me refiero a algo para iniciarme.
Gracias de antemano.

[gnzsoloyo]

El por qué en un caso usa el índice y en el otro no es más o menos simple. Requiere una pequeña explicación:

1. Hay que recordar que los motores de Bases de datos por eficiencia no buscan registro a registro. Levantan bloques de registros en páginas de memoria de alrededor de 64 Kb. Tantos como el espacio que tengan para manejar. La búsqueda se hace en esos bloques, luego mientras menos bloques deban levantar, menor es el tiempo de consulta.

2. El cuantos registros levanta del índice, depende de la longitud de la clave del índice (por eso los fultext son tan problemáticos a la hora de optimizar). No van a entrar más en cada bloque de los que pueden, y en ese sentido mientras menor se ala longitud de la clave, la búsqueda es más rápida. Por definición de modelo, los índices primarios se levantan por bloques de registros índice, mientras que el resto de los índices requieren leer bloques de registros de tabla. Obviamente las cantidades de uno y otro son distintas.

3. Los índices primarios son los mejores, porque cada clave representa un sólo registro. Los INDEX, BTREE y demás, son menos eficientes, porque cada clave es una lista de refrencias a una cantidad variable de registros (conteniendo su PK, habitualmente).

4. Las búsquedas por "=" son las mejores porque hacen uso directo de los índices, y esto requiere que se busque un sólo bloque. El resto de las consultas (< , > , >= , <=, <>), exige levantar tantos registros como los que posiblemente cumplan con las condiciones. Esto puede implicar MUCHOS registros.

5. En el primer caso no usa el índice porque a pesar de existir, la consulta al hacerse por ">=" o "<=", implica hacer una búsqueda binaria de valores en el índice. Estas búsquedas comienzan leyendo las dos cotas superior e inferior de todo el índice, para luego buscar el punto medio de lo resultante, y luego el punto medio de ese resultante, y así hasta encontrar lo buscado (o no). En este sentido, los parsers concluyen que la cantidad de bloques de índice puede ser demasiado parecida a la que usarían leyendo la tabla completa. Entonces, como de todos modos después de leer el índice deberán ir a la tabla, levantan los datos de la tabla directamente.

6. El segundo caso usa el índice porque, a pesar de lo que uno imagine, para el parser, BETWEEN [condición] AND [condición] , [condición] OR [condición] , o bien [condición] AND [condición] , no son UNA búsqueda sino DOS. Primero hacen una (<=)y después hacen la otra (>=), para luego unir resultado. Ahora bien, si la primera llega a barrer la mitad de las tuplas, y la otra también, habremos barrido la totalidad de la tabla buscando datos. No es eficiente, entonces recurre al índice, porque en el peor de los casos, recorrerá la mitad del índice, ya que cada cota se buscará con una de las condiciones.

Esa sería la razón de por qué <= AND >= usa índice, y una sola [condición] no.

Tips de optimización:
1. Cuando debas usar un BETWEEN, ponlo entre paréntesis (esto lo fuerza a hacer la comparación en bloque) y compara los tiempos. Me ha sucedido que en algunos casos reduzca el tiempo hasta en un 50%.
2. Asegúrate de que la consulta incluya todos los campos que compone un índice. Sino, no lo va a usar (simple: no tiene los datos en el SELECT, por lo tanto no puede usar el índice).
3. Verifica que estés realmente usando los índices que has definido. Un índice ocioso es espacio y tiempo de mantenimiento perdido. Todo tiempo y espacio perdido reduce la performance de la base.
4. Aunque los ID autonuméricos son muy buenos, fíjate si puedes usar otro(s) campo(s) para crear la PK. Cuando se trata de campos usados en las consultas, la velocidad de lectura aumenta notablemente. Hace un poco más complicadas las referencias, pero puede ganarse velocidad de respuesta.
5. No selecciones campos que no son estrictamente necesarios. Bytes de mas leídos son tuplas menos en memoria y menos velocidad de respuesta.
6. Si quieres sólo una línea como resultado de tu consulta deberías especificar LIMIT 1. De esta forma MySQL no continua buscando.

Hay mucho más, pero por ahora es suficiente.

[jurena]

Extraordinario, gnzsoloyo.
Muchas gracias. Son lecciones como esta las que necesitamos algunos como yo. En mi inexperta opinión, los moderadores debían subir esta respuesta, retocada en la medida que el autor y los moderadores entiendan, a las FAQ de bases de datos, pues no son pocas las preguntas sobre cómo mejorar el funcionamiento de los índices, y uno siempre se encuentra en esas respuestas el cómo, pero nunca o casi nunca también el por qué. Desde luego esos tips de optimización son más que recomendables: yo trataré de seguir esos consejos.

Un saludo y muchísimas gracias, gnzsoloyo.

[cmunozp]

Gracias, master me quedo super claro que la creacion de un indice para el rango de fechas, es totalmente inecesario ya que mysql evalua si es necesario la utilizacion de este o no.

por otra parte creo que si existen busquedas del tipo = en el campo fecha el indice si funcionaria, de acuerdo a lo que gonzaloyo a explicado.

de acuerdo a esto mismo cuando el usuario selecciona en el rango de fechas el mismo dia, por temas de optimizacion es mejor utilizar el operador =.

saludos y muchas gracias por sus respuestas.

[gnzsoloyo]

Sobre esto último, lo mejor sería saber si existe una variación muy grande de fechas o las mismas se acumulan principalmente en un rango.
Me explico.
Si existe un indice BTREE o HASH definido sobre un DATETIME de una tabla, y dentro del conjunto de registros existe una dispersión homogénea de valores, el índice es muy útil porque permite acotar rápidamente los registros a usar cuando se los compara con <= AND >= .
Pero si la dispesión se acumula en picos de fechas, puede que la cantidad de registros a traer sea casi equivalente a media tabla. Entonces el índice no resulta tan eficiente y es descartable.
¿Como decidir si usarlo o ignorarlo?
Un tip podría ser considerar qué tipo de datos estamos guardando.
Si estamos almacenando registros que se generan entre rangos de fecha más o menos fijos es posible que nos convenga eliminar el índice.
Por ejemplo: las cuotas de una escuela privada, por ejemplo, lo hacen entre el 1 y el 10 de cada mes.
Pero en la emisión de facturas de una estación de combustible, puede que no, porque la dispersión de ventas es mas o menos homogénea a través del mes.

Hay que, entonces, analizar los casos en particular y no caer en generalizaciones que pueden no ser funcionales.

[cmunozp]

se entiende correctamente, cuando hablamos de un rango de fecha generalmente en el caso particular del sistema que manejo es mensual, aunque podria ser diaria u anual, y el nivel de registros mensuales son bastente pequeños hablamos de 200 registros mensuales aprox.

ahora en definitiva se podria concluir que el uso de un indice en una fecha, para trabajar con rango de fechas en el filtro no tendria mayor optimizacion de parte de mysql, esto es en contexto que la data y el rango de fechas sea muy variable.