Indexando tablas en MySQL

[Ronruby]

Saludos,

Mi duda es la siguiente:
Cree una tabla con 8,657,154 registros para practicar como optimizar las tablas.
La tabla que contiene los registros, tiene los campos: idorders, fecha, customerid, productid, amount
(Solamente la cree para probar, en cuanto a diseño se que hay cositas que deben cambiar)
Ahora bien, hago la siguiente consulta SIN INDICES:

Código MySQL:

SELECT
        COUNT(orders.idorders) AS numero
    FROM
        orders
    WHERE
        orders.fecha > '2011-05-01'
    AND
        orders.productid = 1
    ;
 

Y toma alrededor de 1 - 2 segundos. Ahora bien, cree 2 INDICES. Uno para la fecha y otro para el productid pero al correr la MISMA consulta toma de 8 - 10 segundos.

¿Que estoy haciendo mal o que esta pasando?

Nota: Sin embargo al poner EXPLAIN al principio de la consulta claramente se puede ver que usando indices busca en la mínima cantidad de filas que cumplen con el criterio (1,435,812) y que realmente si esta optimizada la consulta. Sin los indices tiene que recorrer los 8,657,154 registros

[gnzsoloyo]

Hay razones para que eso pase, entre ellas la ineficiencia del índice (flata de selectividad, puede estar requiriendo recorridos demasiado grandes)
Prueba crear un indice con los dos al mismo tiempo:

Código MySQL:

CREATE INDEX fecha_ids ON orders(fecha, idorders);
 

y cuéntanos luego qué tal te fue...

[Ronruby]

Sigue igual de ineficiente. Aun sin indices sigue siendo mas rápida la consulta.

¿Alguna otra idea?

[Giolink1]

Utiliza índices únicos, los índices trabajan mejor sobre columnas con valores único, y peor con aquellas que tiene muchos valores duplicados. Saludos :)

[gnzsoloyo]

Iniciado por Giolink1

Utiliza índices únicos, los índices trabajan mejor sobre columnas con valores único, y peor con aquellas que tiene muchos valores duplicados. Saludos :)

Un indice UNIQUE es en esencia la declaración de una clave candidata, y es inaplicable en el caso de este thread (que por si no lo notaste, es demasiado viejo).
En realidad el problema es que quiere contar los diferentes ID de los registros, por lo que lo que hace MYSQL es acudir al indice primario, y en ese caso el resultado será siempre el mismo, porque siempre usará sólo ese índice.
Para que se pueda pueda optimizar, habría que omitir ese campo en el COUNT y el ID en el WHERE. Recién allí un indice por fecha daría algún resultado.
Y el hecho que los UNIQUE sean más "eficientes", no implica que sean los mejores en un caso. Eso no lo define la unicidad de la clave3, sino la cantidad de registros devueltos, que en el caso de buscar rangos de fechas los INDEX, muy probablemente sean mejores.
Además, recuerda que a más indices en las tablas, la performance global se reduce. No siempre es mejor. Por eso la optimziación tiene reglas globales, pero no se pueden aplicar a todos los casos. Cada caso debe ser analizado en el contexto del sistema y de los datos.