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.

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

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

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;

select id,nick from users as Usuario  where email ='pedro@domino.com';

Algo muy simple,no?
Pues veamos que indices tiene asignados. Esto se puede comprobar poniendo delante de la sql la sentencia EXPLAIN.

explain select id,nick from users as Usuario  where email ='pedro@domino.com';

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.

Sql explain
Sql explain. Pulsa para ver a mayor tamaño

Para modificar un indice se puede hacer de la siguiente manera

alter table users add index `idx_nick` (`nick_usuario`);

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

alter table users add index `idx_logins` (`id`, `username`, `status`, `email`)

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.

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

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

4 opiniones en “Optimizando consultas mysql. Creando y definiendo indices. Manage index”

  1. Me agrada este post =). Precisamente estoy buscando información de los indices ( index ) en mysql por que quiero saber para que se utilizan, este articulo me aclara un poco la duda, pero sigo investigando. Tambien estoy buscando informacion sobre las bases de datos mysql que sean de tipo myisam. Quiero saber si puedo utilizar claves foranes y como utilizarla. Gracias por el post

  2. Hola,

    Estoy creando una base de datos para gestionar las reservas de alojamiento de clientes y me gustaria indexar la tabla por apellido1+apellido2+nombre y todavia no lo acabo de ver claro de como hacerlo.

    Igualmente, ¿como puedo conseguir crear una busqueda tipo barra de google? Segun el usuario vaya tecleando vayan apareciendo en la pantalla los resultados que mas se aproximan

    Muchas gracias y felicidades por el articulo.

    jordi

    1. Respondo tu segunda pregunta, ese tipo de busquedas se desarrollan con AJAX, y lo que hace es que cada vez que ocurra un cambio en la caja de texto AJAX inyectara el query tantas veces el usuario teclee.

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *