Los índices en MySQL

¿Que es un índice?

Un índice (o KEY, o INDEX) es un grupo de datos que MySQL asocia con una o varias columnas de la tabla. En este grupo de datos aparece la relación entre el contenido y el número de fila donde está ubicado.

Los índices -como los índices de los libros- sirven para agilizar las consultas a las tablas, evitando que mysql tenga que revisar todos los datos disponibles para devolver el resultado.

Podemos crear el índice a la vez que creamos la tabla, usando la palabra INDEX seguida del nombre del índice a crear y columnas a indexar (que pueden ser varias):
INDEX nombre_indice (columna_indexada, columna_indexada2...)

La sintaxis es ligeramente distinta segun la clase de índice:

PRIMARY KEY (nombre_columna_1 [,nombre_columna2...])
UNIQUE INDEX nombre_indice (columna_indexada1 [,columna_indexada2 ...])
INDEX nombre_index (columna_indexada1 [,columna_indexada2...]) 

Podemos también añadirlos a una tabla después de creada:
ALTER TABLE nombre_tabla ADD INDEX nombre_indice (columna_indexada);

Si queremos eliminar un índice: ALTER TABLE tabla_nombre DROP INDEX nombre_indice¿para que sirven ?

LOs index permiten mayor rápidez en la ejecución de las consultas a la base de datos tipo SELECT ... WHERE

La regla básica es pues crear tus índices sobre aquellas columnas que vayas a usar con una cláusula WHERE, y no crearlos con aquellas columnas que vayan a ser objeto de un SELECT: SELECT texto from tabla_libros WHERE autor = Vazquez; En este ejemplo, la de autor es una columna buena candidata a un indice; la de texto, no.

Otra regla básica es que son mejores candidatas a indexar aquellas columnas que presentan muchos valores distintos, mientras que no son buenas candidatas las que tienen muchos valores idénticos, como por ejemplo sexo (masculino y femenino) porque cada consulta implicará siempre recorrer practicamente la mitad del indice.

La regla de la izquierda

Si necesitamos un select del tipo SELECT ... WHERE columna_1 = X AND columna_2 = Y y ya tenemos un INDEX con la columna_1, podemos crear un segundo indice con la columna 2, o mejor todavía, crear un único indice combinado con las columnas 1 y 2. Estos son los índices multicolumna, o compuestos.

No obstante si tienes indices multicolumna y los utilizas en las clausulas WHERE, debes incluir siempre de izquierda a derecha las columnas indexadas; o el indice NO se usará:

Supongamos un INDEX usuario (id, name, adress), y una cláusula SELECT ... WHERE NAME = x. Este Select no aprovechará el índice. Tampoco lo haría un SELECT ... WHERE ID =X AND ADRESS = Y. Cualquier consulta que incluya una columna parte del index sin incluir además las columnas a su izquierda, no usará el indice.

Por tanto en nuestro ejemplo solo sacarian provecho del indice las consultas SELECT ... WHERE ID = x, o WHERE ID = X AND NAME = y o WHERE ID = x AND NAME = y AND ADRESS = Z

Cuando un índice contiene mas de una columna, cada columna es leida por el orden que ocupa de izquierda a derecha, y a efectos prácticos, cada columna (por ese orden) es como si constituyera su propio índice. Esto significa que en el ejemplo anterior, no haría falta crear otro INDEX ID (id) ya que podríamos usar nuestro INDEX USUARIO simplemente con la cláusula SELECT ... WHERE ID = X;

Puedes ver si tu llamada sql usa o no los índices correctos anteponiendo a select la orden explain:
EXPLAIN SELECT * FROM mitable WHERE ....
Y para ser sinceros, usando explain para comprobar el uso de indices en distintos selects con indices multicolumna, he obtenido resultados poco consistentes con la 'regla de la izquierda' ya que en muchos casos parece que se usaban indices que teóricamente no debian estar disponibles ... posiblemente un caso de mala configuracion en mi tabla-test

Usuarios que han visto este tema también han visto...

- Guardar y extraer imágenes en MySQL
- Búsquedas con Full-text y MySQL
- Múltiples instalaciones de MySQL en tu PC
- Conceder y quitar privilegios en MySQL
- Resolver Problemas de Autenticacion con MySQL

- Versión imprimible de este documento
- Enviar por e-mail este documento