Capítulo 8: Optimización

Optimización de consultas

Una sencilla consulta:

USE videoteca;

SELECT
  *
FROM
  pelicula
WHERE
  titulo = 'Blade Runner';

Plan de ejecución

Para ver qué hace una consulta:

USE videoteca;

EXPLAIN
SELECT
  *
FROM
  pelicula
WHERE
  titulo = 'Blade Runner'G

Índices

Crear un índice para el título:

USE videoteca;

ALTER TABLE
  pelicula
ADD INDEX
  pelicula_titulo_indice(titulo);

Crear el índice, dándole un nombre más corto:

USE videoteca;

ALTER TABLE
  pelicula
ADD INDEX
  pt_I(titulo);

Índices únicos

Crear la tabla de intérpretes con un índice único:

USE videoteca;

CREATE TABLE interprete
(
  id INT NOT NULL AUTO_INCREMENT,
  nombre VARCHAR(64) NOT NULL,
  apellidos VARCHAR(64) NOT NULL,
  imdb VARCHAR(32) NOT NULL DEFAULT '',
  PRIMARY KEY(id),
  UNIQUE INDEX ii_UI(imdb)
)
ENGINE = InnoDB;

O modifica el diseño de la existente:

USE videoteca;

ALTER TABLE
  interprete
ADD UNIQUE INDEX
  ii_UI(imdb);

Lo mismo con los directores:

USE videoteca;

ALTER TABLE
  director
ADD UNIQUE INDEX
  di_UI(imdb);

Índice de texto

Tabla intermedia:

USE videoteca;

CREATE TABLE lomo
(
  idpelicula INT NOT NULL,
  titulo VARCHAR(64) NOT NULL,
  PRIMARY KEY(idpelicula),
  FULLTEXT INDEX lt_FT(titulo)
)
ENGINE = InnoDB;

Llenado inicial de la tabla:

USE videoteca;

INSERT INTO
  lomo(idpelicula, titulo)
SELECT
  id,
  titulo
FROM
  pelicula;

Uso del índice:

USE videoteca;

SELECT
  *
FROM
  lomo
WHERE
  MATCH(titulo) AGAINST('blade');

Otro uso del índice:

USE videoteca;

SELECT
  p.*
FROM
  pelicula p
  JOIN lomo l ON p.id = l.idpelicula
WHERE
  MATCH(l.titulo) AGAINST('blade');

Borrado de índices

Este índice ya no sirve:

USE videoteca;

ALTER TABLE
  pelicula
DROP INDEX
  pt_I;

Información sobre índices

Descripción de una tabla:

USE videoteca;

DESCRIBE lomo;

Detalles de los índices:

USE videoteca;

SHOW INDEX FROM lomo;

Consulta de todos los detalles de una película:

USE videoteca;

SELECT
  p.titulo pelicula,
  s.nombre soporte,
  g.descripcion genero,
  CONCAT(d.nombre, ' ', d.apellidos) director,
  CONCAT(i.nombre, ' ', i.apellidos) interprete
FROM
  pelicula p
  JOIN genero g ON p.idgenero = g.id
  JOIN soporte s ON p.idsoporte = s.id
  JOIN directores_por_pelicula dpp ON p.id = dpp.idpelicula
  JOIN director d ON dpp.iddirector = d.id
  JOIN interpretes_por_pelicula ipp ON p.id = ipp.idpelicula
  JOIN interprete i ON ipp.idinterprete = i.id
WHERE
  p.titulo = 'Blade Runner';

Obligar a utilizar las tablas en el orden indicado:

USE videoteca;

SELECT STRAIGHT_JOIN
  p.titulo pelicula,
  s.nombre soporte,
  g.descripcion genero,
  CONCAT(d.nombre,' ', d.apellidos) director,
  CONCAT(i.nombre,' ', i.apellidos) interprete
FROM
  pelicula p
  JOIN genero g ON p.idgenero = g.id
  JOIN soporte s ON p.idsoporte = s.id
  JOIN directores_por_pelicula dpp ON p.id = dpp.idpelicula
  JOIN director d ON dpp.iddirector = d.id
  JOIN interpretes_por_pelicula ipp ON p.id = ipp.idpelicula
  JOIN interprete i ON ipp.idinterprete = i.id
WHERE
  p.titulo = 'Blade Runner';