porque este indice no funciona??

[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.