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:
- 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.
- 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:
- 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...]));
- 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:
- Crear el �ndice al momento de crear la tabla.
CREATE TABLE nombreTabla( campo1 TIPO, campo2 TIPO, FULLTEXT [nombreIndice] (campo1 [campo2,...]) );
- 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:
- 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...]));
- 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));