P11 Indices y explain plan

Gestión de índices en MySQL

Los índices son estructuras en árbol opcionales, asociadas con tablas, que permiten un camino de acceso más rápido a las filas de una tabla de datos.

Sin los índices, MySQL debe recorrer una a una las filas de una tabla hasta encontrar aquellas que sean relevantes.

Crear índices

Sintaxis

CREATE INDEX nombreindice

ON nombretabla (col [,col2…]);

La siguiente sentencia crea un índice llamado idx_profesor_nombre, sobre la columna nombre de la tabla profesor.

CREATE INDEX idx_profesor_nombre

ON profesor (nombre);

Borrar índices

Para borrar un índice:

DROP INDEX nombreindice

ON nombre tabla;

La siguiente sentencia borra un índice de nombre idx_profesor_nombre creado sobre la tabla profesor.

DROP INDEX idx_profesor_nombre

ON profesor;

Índices de clave primaria

MySQL crea un índice de tipo primary key con cada clave primaria al crear una tabla. Así, la sentencia siguiente crea la tabla imparte con la restricción de clave primaria en las columnas (prof, asig), y un índice de nombre PRIMARY sobre las columnas (prof,asig) de la tabla imparte.

CREATE TABLE imparte (prof varchar(10), asig varchar(10), PRIMARY KEY (prof, asig));

Consultar índices

Para consultar los índices que se han creado asociados a una tabla se utiliza la sentencia SHOW INDEX:

SHOW INDEX FROM profesor;

Mostrará la tabla (columna table) sobre la que se ha definido el índice, su nombre (columna key_name) y la columna sobre la que se ha definido (columna column_name). En el caso de que sea un índice definido sobre más de una columna, se mostrarán tantas filas como columnas forman el índice.

Consultar el plan de ejecución

El conjunto de operaciones que el módulo optimizador de MySQL selecciona para resolver una consulta se denomina plan de ejecución. La sentencia EXPLAIN proporciona información acerca del plan de ejecución de cualquier sentencia.

EXPLAIN

SELECT *

FROM imparte

WHERE dni=’1’;

Su ejecución devuelve una fila con diferente información. De momento nos centraremos en las columnas: possible_keys (posibles índices a elegir) key (índice elegido).