Optimizando consultas mysql. Creando y definiendo indices. Manage index

Los índices se utilizan para buscar las filas con valores de columna específica rápidamente. Sin un índice, MySQL debe comenzar con el registro primero y luego leer a través de toda la tabla para buscar las filas correspondientes. Cuanto más grande sea la tabla, más tarda este proceso. Si la tabla tiene un índice para las columnas que se trate, MySQL puede determinar rápidamente la posición de buscar en el medio del archivo de datos sin tener que mirar todos los datos. Si una tabla tiene 1000 filas, entonces esto es por lo menos 100 veces más rápido que la lectura secuencial.
Todo esto puede conllevar que si una tabla tiene como Engine MyISAM puede lugar a bloqueos mientras ésta se esta leyendo.

Pues bien como indico los índices aumenta la velocidad de la consulta y es crítico cuando tenemos un tráfico medio-alto. Si los indices no están bien configurados o no se tienen, puede ser problema de que nuestro mysql se bloque, toda la web vaya lenta o incluso que el servidor se bloque y haya que reiniciarlo o se caiga directamente.

Normalmente un indice se crea dependiendo de la búsqueda que se haga, combinando lo que haya en la sentencia SELECT y WHERE.

La sql para crear un indice es la siguiente.

[code]
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
[USING index_type]
ON tbl_name (index_col_name,…)

index_col_name:
col_name [(length)] [ASC | DESC]
[/code]

veamos ejemplos más claros

Si seleccionamos el identificador de usuario y el nick en una tabla usuarios buscando por el email, tendremos una sql como la siguiente;
[code]
select id,nick from users as Usuario where email =’[email protected]’;
[/code]

Algo muy simple,no?
Pues veamos que indices tiene asignados. Esto se puede comprobar poniendo delante de la sql la sentencia EXPLAIN.
[code]
explain select id,nick from users as Usuario where email =’[email protected]’;
[/code]

Esto nos puede mostrar algo como lo que viene en la siguiente imagen.

Nota: en la sql original que arroja esta captura se hace un inner join compuesto con 4 tablas. Es en estos casos donde la sql puede tardar más y donde es mucho más necesario el uso de indices.

Para modificar un indice se puede hacer de la siguiente manera

[code]
alter table users add index `idx_nick` (`nick_usuario`);
[/code]

Donde idx_nick es el nombre del indice que estamos creando y nick_usuario el campo en la tabla que vamos a indexar.

Para añadir un nuevo indice compuesto se puede hacer de la siguiente manera

[code]
alter table users add index `idx_logins` (`id`, `username`, `status`, `email`)
[/code]

Esto puede dar lugar a veces a que el tamaño del indice que estamos generando es demasiado grande.

specified key was too long; max key lenght is 1000 bytes

Para ello vamos ajustando el tamaño que asignamos a cada campo para el indice.

[code]
alter table users add index `idx_logins` (`id`, `username`(200), `status`, `email`(100))
[/code]

referencias:
http://dev.mysql.com/doc/refman/5.0/es/create-index.html