manera correcta de crear un indice en tabla

[pithon]

Hola, no acabo de entender como crear los indices en las tablas, partiendo de una tabla como esta:

tabla
id
identificador
nombre
correcto
caducado
descripcion
preferencias
fecha


consulta que se realiza

Código MySQL:

SELECT * FROM tabla WHERE nombre='andres' AND descripcion='estudiante' AND correcto=0 AND caducado > CURRENT_TIMESTAMP();
 

los indices los cree de la siguiente manera
id,identificador : primary, unique aqui no tengo dudas.

en cambio en los otros cree un indice para todas las columnas aunque la columna preferencia no la utilice en esta consulta la utilizare en otra que sustituira al campo descripcion, esta es la manera correcta alguien me ayuda a entender como funcionan los indices para que las consultas sean mas rapidas, gracias de antemano.

[Libras]

entiendes lo que es un indice????

[pithon]

Hola libras si entiendo que es un índice, aplicando la lógica creo que solo debería crear un índice en la columna que menos resultados tenga, aunque no estoy muy seguro.

[Libras]

Un indice es una forma de ordenar los datos ya sea fisica(clustered) o logica (non clustered), asi que el indice se debe de crear sobre las columnas claves de la tabla no sobre todas las columnas, digamos tenemos la tabla alumnos(clasica)

alumnos
id
nombre
fecha_nacimiento
direccion

ahora esta tabla podria tener un indice clustered(fisico) en la columna Id que es la que mas se estara utilizando en los queries, relaciones y demas, y podrias crear un non clustered en las columnas nombre y fecha_nacimiento que seria los otros campos por los cuales podriamos realizar busquedas......

[gnzsoloyo]

Nada que ver. Eso sugiere que no dominas el concepto.
Los índices se crean para optimizar las búsquedas, lo que no implica que sea sobre una o múltiples columnas, sino sobre aquellos datos que se usan en las más habituales.
Por otro lado, como tienen impacto en la performance de altas, bajas y modificaciones, no es conveniente abusar de ellos, ya que generarían problemas al requerir constantemente acceso para escritura.
La decisión de crear índices no se toma a la ligera.

[pithon]

Hola gracias a los dos, mas o menos lo entiendo, quizas no me explique bien no seria crear el indice en la columna que menos resultados tenga si no la columna mas utilizada como comenta libras.

Igual que en el ejemplo de libra cree los indices en los campos nombre y descripcion pero la consulta se vuelve un poco mas lenta que si no esta, use explain y me recorre menos filas pero tarda mas que puede estar pasando.

gnzsolo se que hay que tener cuidado con los indices sobretodo por los insert,update y delete entre otras cosas pero mis consultas son lentas y esas ejecuciones apenas se utilizan, que puedo comprobar para ver que esta pasando en mis consultas, en la tabla hay 100.000 registros.
saludos

[Libras]

ahi si en mysql no se como se haga el analisis de los querys puede ser muchos factores por los cuales un indice no mejore la rapidez de un query.....

[pithon]

hice un explain y me dice que las filas afectadas es la mitad de lo normal pero aun asi la consulta tardo mas con el indice, si alguiien sabe alguna manera de saber donde esta el problema me gustaria saberlo, saludos.

[gnzsoloyo]

Iniciado por pithon

hice un explain y me dice que las filas afectadas es la mitad de lo normal pero aun asi la consulta tardo mas con el indice, si alguiien sabe alguna manera de saber donde esta el problema me gustaria saberlo, saludos.

Habría que ver cómo es la consulta creada. Hablando en el aire no podemos saber por qué hace lo que hace.
Por otro lado, habría que ver qué nivel de selectividad tiene el índice creado, y si el WHERE que se usa utiliza todas las columnas del indice o sólo parte de ellas.
En principio, si una consulta usa un índice, y el índice en cuestión devuelve alrededor de la mitad de los registros de la tabla, es evidente que es un índice ineficiente, o el WHERE lo es, ya que en ese caso MySQL podría descartar el índice y terminar haciendo un full table scan.

¿Podrías dar un ejemplo de consulta usada?

[pithon]

Hola gnzsoloyo la consulta es algo como esta:

Código MySQL:

SELECT * 
FROM tabla 
WHERE nombre='andres' AND descripcion='estudiante' AND correcto=0 AND caducado > CURRENT_TIMESTAMP();
 

Cree dos indices para nombre y descripción

Código MySQL:

Create index i_tabla on tabla (nombre,descripcion );
 

En el explain me dice que están los dos indices posibles pero luego solo utiliza uno. Alguna conclusión de mi error?
Saludos

[gnzsoloyo]

En principio, si en el WHERE se usan ambos campos del índice, es razonable que acceda al índice. La duda es cuáles son los resultados en cuanto a lectura de registros.

Empecemos con esto:
1) Dinos qué cambia de la performance cuando quitas la última condición, la de "caducado":

Código MySQL:

SELECT *
FROM tabla
WHERE nombre='andres' 
    AND descripcion='estudiante' 
    AND correcto=0 
    -- AND caducado > CURRENT_TIMESTAMP() # Desactivamos esta condición
;
 

El objetivo de esta prueba es verificar si el ">" genera un overhead mayor, ya que cualquier condición comparativa de >, <, >=, o <=, suele tener menor performance que la de un "=".

2) Posteanos el resultado completo del EXPLAIN. Así podremos ver cuál es el resultado de la lectura de registros. Eso nos puede indicar con mas claridad lo que ese indice aporta.

3) Para una mayor prueba, quita el índice de doble columna y crea dos indices, uno por cada una de ellas. Luego vuelve a probar.

[pithon]

Hola gnzsoloyo, quite la condicion del comparador > y la consulta tardo menos pero sigo con el problema de indices, borre los indices y los cree uno para cada columna este es el explain:

+----+-------------+---------+------+-------------------------+-------------+---------+-------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+-------------------------+-------------+---------+-------+-------+-------------+
| 1 | SIMPLE | table | ref | i_nombre,i_descripcion | i_nombre | 77 | const | 52866 | Using where |
+----+-------------+---------+------+-------------------------+-------------+---------+-------+-------+-------------+



los registros en las tabla son 100.000 y me devuelve 60.000 en la consulta posteada tardandome 0.50segundos y con los indices 1.1segundo.
saludos.

añadir que los registros nombre y descripcion tienen muchos valores duplicados no se si esto afecta y es mejor no crearlos.