Guía Práctica MySQL 5/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 actores con un índice único:

USE videoteca;

CREATE TABLE actor (
  id INTEGER UNSIGNED 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 ai_UI(imdb)
)
ENGINE = InnoDB;

O modifique el diseño de la existente:

USE videoteca;

ALTER TABLE
  actor
ADD UNIQUE INDEX
  ai_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 INTEGER UNSIGNED NOT NULL,
  titulo VARCHAR(64) NOT NULL,
  PRIMARY KEY(idpelicula),
  FULLTEXT INDEX lt_FT(titulo)
)
ENGINE = MyISAM;

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 ínidices

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(a.nombre,' ',a.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 actores_por_pelicula app
    ON p.id=app.idpelicula
  JOIN actor a
    ON app.idactor=a.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(a.nombre,' ',a.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 actores_por_pelicula app
    ON p.id=app.idpelicula
  JOIN actor a
    ON app.idactor=a.id
WHERE
  p.titulo = 'Blade Runner';