Índices y optimización de consultas

Existen cuatro tipos de �ndices que podemos utilizar en MySQL; de clave primaria, �nicos, de texto completo, y ordinarios. Cada uno de ellos ser� explicado a continuaci�n.

��ndices de clave primaria

Una clave primaria es un �ndice sobre uno o m�s campos donde cada valor es �nico y ninguno de los valores son NULL.

Para crear un �ndice de clave primaria tenemos b�sicamente dos opciones:

1. Crear el �ndice de clave primaria al momento de crear la tabla. En este caso se usa la opci�n PRIMARY KEY al final de la definici�n de los campos, con una lista de los campos que ser�n parte del �ndice.

   CREATE TABLE nombreTabla(campo1 tipoDato,
    [campo2...,] PRIMARY KEY (campo1 [,campo2...]) );

   Hacemos �nfasis en que la palabra clave NOT NULL es obligatoria para un campo cuando �ste vaya a formar parte de una clave primaria; como mencionamos anteriormente, las claves primarias no pueden contener valores nulos. Si intentamos crear una clave primaria sobre un campo nulo, MySQL nos marcar� un error.

2. Crear una clave primaria en una tabla existente con el uso del comando ALTER TABLE:

   ALTER TABLE nombreTabla ADD PRIMARY KEY(campo1 [,campo2...]);

Por ejemplo, suponiendo que ya tenemos en nuestro sistema una tabla que fue creada de la siguiente manera (sin clave primaria, y con el campo id aceptando valores NULL):

CREATE TABLE usuarios(id int, nombre varchar(50), apellidos varchar(70));

Podemos crear una clave primaria sobre el campo id con esta sentencia:

ALTER TABLE usuarios MODIFY id INT NOT NULL, ADD PRIMARY KEY(id);

Para observar los cambios que hemos hecho, podemos examinar las columnas de la tabla usuarios con una sentencia DESCRIBE:

mysql> DESCRIBE usuarios;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id        | int(11)     |      | PRI | 0       |       |
| nombre    | varchar(50) | YES  |     | NULL    |       |
| apellidos | varchar(70) | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

El campo id no tiene un valor YES en la columna Null, lo que indica que este campo ya no podr� almacenar valores nulos. Se puede observar tambi�n que se tiene un valor PRI en la columna Key, lo que indica que este campo es una clave primaria.

Las claves primarias pueden constar de m�s de un campo. Hay algunas veces en las que un solo campo no puede identificar de manera �nica a un registro.

��ndices ordinarios

Un �ndice que no es primario permite valores duplicados (a menos que los campos hayan sido especificados como UNIQUE).

Para crear un �ndice ordinario tenemos b�sicamente dos opciones:

1. Podemos crear un �ndice ordinario al mismo tiempo que creamos la tabla con el uso de la opci�n INDEX.

   CREATE TABLE nombreTabla(campo1 tipoDato, campo2 tipoDato,..
     INDEX [nombreIndice] (campo1 [,campo2...]));

2. De igual manera, podemos crear el �ndice con el uso de la sentencia ALTER TABLE si es que la tabla ya existe.

   ALTER TABLE nombreTabla ADD INDEX [nombreIndice] (campo1 [,campo2...]);

   Tambi�n es posible usar la sentencia CREATE INDEX para crear un �ndice en una tabla existente.

   CREATE INDEX nombreIndice ON nombreTabla(campo1 [,campo2...]);

   Ambas sentencias piden el nombre del �ndice, sin embargo con la sentencia CREATE INDEX el nombre es obligatorio.

Por ejemplo, para la siguiente definici�n de tabla:

CREATE TABLE usuarios(id int, nombre varchar(50), apellidos varchar(70));

Se puede crear un �ndice en la columna apellidos con una sentencia ALTER TABLE:

ALTER TABLE usuarios ADD INDEX idx_apellidos (apellidos);

O bien, con una sentencia CREATE INDEX:

CREATE INDEX idx_apellidos ON usuarios(apellidos);

��ndices de texto completo

Los �ndices de texto completo son del tipo FULLTEXT, se usan en tablas del tipo MyISAM, y pueden contener uno o m�s campos del tipo CHAR, VARCHAR y TEXT. Un �ndice de texto completo est� dise�ado para facilitar y optimizar la b�squeda de palabras clave en tablas que tienen grandes cantidades de informaci�n en campos de texto.

Para crear un �ndice de texto completo tenemos b�sicamente dos opciones:

1. Crear el �ndice al momento de crear la tabla.

   CREATE TABLE nombreTabla( campo1 TIPO, campo2 TIPO,
   FULLTEXT [nombreIndice] (campo1 [campo2,...]) );

2. Crear el �ndice una vez que ha sido creada la tabla.

   ALTER TABLE nombreTabla ADD FULTEXT [nombreIndice] (campo1 [,campo2,...]);

   La siguiente sentencia tambi�n se puede usar para crear un �ndice cuando la tabla ya existe.

   CREATE FULLTEXT INDEX nombreIndice ON nombreTabla(campo1 [,campo2,...]);

Unicamente para fines ilustrativos, consideremos la siguiente definici�n de tabla:

CREATE TABLE usuarios(id int, nombre varchar(50), apellidos varchar(70));

Podr�amos crear un �ndice FULLTEXT en la columna nombre, en la columna apellidos, o bien, un �ndice que ocupe ambos campos. A continuaci�n se muestran los tres casos.

CREATE FULLTEXT INDEX idx_nombre ON usuarios(nombre);

CREATE FULLTEXT INDEX idx_apellidos ON usuarios(apellidos);

CREATE FULLTEXT INDEX idx_nombre_apellidos ON usuarios(nombre,apellidos);

Nota: Cuando se tienen grandes cantidades de datos, es mucho m�s r�pido cargar los datos en una tabla que no tiene �ndices de texto completo y despu�s crear los �ndices necesarios, ya que la carga de datos en una tabla que ya tiene �ndices de este tipo es un proceso lento.

��ndices �nicos

Los �ndices �nicos son b�sicamente como los �ndices ordinarios, excepto que los valores duplicados no son permitidos.

Para crear un �ndice UNIQUE se tienen b�sicamente dos opciones:

1. Crear un �ndice �nico cuando la tabla es creada con el uso de la opci�n UNIQUE.

   CREATE TABLE nombreTabla(campo1 tipoDato, campo2 tipoDato,..
     UNIQUE [nombreIndice] (campo1 [,campo2...]));

2. O bien, si la tabla ya existe, se usa la sentencia ALTER TABLE.

   ALTER TABLE nombreTabla ADD UNIQUE [nombreIndice] (campo1, campo2) ...

   De igual manera, tambi�n es posible usar la sentencia CREATE INDEX para crear un �ndice �nico en una tabla existente.

   CREATE UNIQUE INDEX nombreIndice ON nombreTabla(campo1 [,campo2...]);

Unicamente para fines ilustrativos, consideremos de nuevo la siguiente definici�n de tabla:

CREATE TABLE usuarios(id int, nombre varchar(50), apellidos varchar(70));

Podr�amos crear un �ndice UNIQUE en la columna nombre, y un �ndice UNIQUE en la columna apellidos.

ALTER TABLE usuarios ADD UNIQUE idx_nombre (nombre);

CREATE UNIQUE INDEX idx_apellidos ON usuarios(apellidos);

En el primer caso hacemos uso del comando ALTER TABLE, y en el segundo caso creamos el �ndice con la sentencia CREATE INDEX.

��ndices compuestos

Los �ndices compuestos son simplemente aquellos que est�n basados en m�ltiples columnas. MySQL �nicamente usa un �ndice por tabla cuando est� procesando una consulta. Esto significa que si tenemos varias columnas que frecuentemente aparecen juntas en una cl�usula WHERE, tenemos la oportunidad de acelerar estas consultas al crear un �ndice compuesto.

Si una tabla tiene un �ndice formado por m�ltiples columnas, cualquier prefijo m�s a la izquierda puede ser usado por el optimizador de consultas de MySQL para encontrar las filas. Por ejemplo, si tenemos un �ndice compuesto por tres columnas (col1, col2, col3), tendr�amos capacidades de b�squeda en (col1), (col1, col2) y (col1, col2, col3).

MySQL no puede usar un �ndice parcial si las columnas no forman un prefijo m�s a la izquierda del �ndice. Supongamos que tenemos unas sentencias SELECT como estas:

mysql> SELECT * FROM algunaTabla WHERE col1=valor1;
mysql> SELECT * FROM algunaTabla WHERE col2=valor2;
mysql> SELECT * FROM algunaTabla WHERE col2=valor2 AND col3=valor3;

Si el �ndice existe en (col1, col2, col3), s�lo la primera de estas consultas usar� el �ndice. La segunda y la tercera involucran a las columnas en el �ndice, pero (col2) y (col2, col3) no son los prefijos m�s a la izquierda de (col1, col2, col3).

Este es otro ejemplo. Consideremos la siguiente definici�n de una tabla:

CREATE TABLE usuarios(id int, nombre varchar(50), apellidos varchar(70));

Si frecuentemente hacemos consultas en la tabla usuarios basadas en el nombre como en los apellidos, podemos beneficiarnos de un �ndice compuesto en las columnas nombre y apellidos.

ALTER TABLE usuarios ADD INDEX idx_nombre(nombre, apellidos);

Debido a la forma en que MySQL construye los �ndices compuestos, �ste puede usar el �ndice idx_nombre para resolver consultas basadas s�lo en el nombre, o en el nombre y los apellidos, sin embargo, no usar� el �ndice en una consulta que haga referencia �nicamente a la columna apellidos.

Por ejemplo, de las siguientes tres consultas, s�lo las dos primeras har�an uso de nuestro �ndice idx_nombre.

SELECT * FROM usuarios WHERE nombre='Eduardo';
SELECT * FROM usuarios WHERE nombre='Eduardo' AND apellidos='Zarate M';
SELECT * FROM usuarios WHERE apellidos='Zarate M';

La idea es que los �ndices compuestos pueden usarse frecuentemente para acelerar algunas consultas complejas, pero necesitamos entender sus limitaciones y debemos ejecutar alg�n tipo de prueba en vez de asumir que estos �ndices siempre nos van a ayudar.

��ndices de parte de campos

En las columnas CHAR y VARCHAR se nos permite crear un �ndice que no use el campo por completo. Retomemos el ejemplo anterior de la tabla usuarios. A pesar de que el nombre de una persona puede ser de hasta 50 caracteres, es muy com�n que los nombres de las personas sean diferentes en los primeros 10 caracteres. Al usar un �ndice de 10 caracteres en lugar de 50, el �ndice ser� m�s peque�o, y permitir� que las consultas INSERT y UPDATE sean m�s r�pidas, a la vez que no se afecta la velocidad de las consultas SELECT.

Para crear un �ndice como parte de un campo, s�lo se tiene que especificar el tama�o entre par�ntesis despu�s del nombre de la columna. Por ejemplo, nuestro �ndice idx_nombre pudo haber sido creado tambi�n de la siguiente manera:

ALTER TABLE usuarios ADD INDEX idx_nombre(nombre(10), apellidos(20));