Actividad #21

Que es un índice?
Un índice (o KEY, o INDEX) es un grupo de datos que MySQL asocia con una o varias columnas de la tabla. En este grupo de datos aparece la relación entre el contenido y el número de fila donde está ubicado.
Los índices -como los índices de los libros- sirven para agilizar las consultas a las tablas, evitando que mysql tenga que revisar todos los datos disponibles para devolver el resultado.
Podemos crear el índice a la vez que creamos la tabla, usando la palabra INDEX seguida del nombre del índice a crear y columnas a indexar (que pueden ser varias):
INDEX nombre_indice (columna_indexada, columna_indexada2...)

La sintaxis es ligeramente distinta segun la clase de índice:

PRIMARY KEY (nombre_columna_1 [,nombre_columna2...])
UNIQUE INDEX nombre_indice (columna_indexada1 [,columna_indexada2 ...])
INDEX nombre_index (columna_indexada1 [,columna_indexada2...]) 

Podemos también añadirlos a una tabla después de creada:
ALTER TABLE nombre_tabla ADD INDEX nombre_indice (columna_indexada);
Si queremos eliminar un índice: ALTER TABLE tabla_nombre DROP INDEX nombre_indice¿para que sirven ?
LOs index permiten mayor rápidez en la ejecución de las consultas a la base de datos tipo SELECT ... WHERE
La regla básica es pues crear tus índices sobre aquellas columnas que vayas a usar con una cláusula WHERE, y no crearlos con aquellas columnas que vayan a ser objeto de un SELECT: SELECT texto from tabla_libros WHERE autor = Vazquez; En este ejemplo, la de autor es una columna buena candidata a un indice; la de texto, no.

Otra regla básica es que son mejores candidatas a indexar aquellas columnas que presentan muchos valores distintos, mientras que no son buenas candidatas las que tienen muchos valores idénticos, como por ejemplo sexo (masculino y femenino) porque cada consulta implicará siempre recorrer practicamente la mitad del indice.
La regla de la izquierda
Si necesitamos un select del tipo SELECT ... WHERE columna_1 = X AND columna_2 = Y y ya tenemos un INDEX con la columna_1, podemos crear un segundo indice con la columna 2, o mejor todavía, crear un único indice combinado con las columnas 1 y 2. Estos son los índices multicolumna, o compuestos.
No obstante si tienes indices multicolumna y los utilizas en las clausulas WHERE, debes incluir siempre de izquierda a derecha las columnas indexadas; o el indice NO se usará:
Supongamos un INDEX usuario (id, name, adress), y una cláusula SELECT ... WHERE NAME = x. Este Select no aprovechará el índice. Tampoco lo haría un SELECT ... WHERE ID =X AND ADRESS = Y. Cualquier consulta que incluya una columna parte del index sin incluir además las columnas a su izquierda, no usará el indice.

Por tanto en nuestro ejemplo solo sacarian provecho del indice las consultas SELECT ... WHERE ID = x, o WHERE ID = X AND NAME = y o WHERE ID = x AND NAME = y AND ADRESS = Z
Cuando un índice contiene mas de una columna, cada columna es leida por el orden que ocupa de izquierda a derecha, y a efectos prácticos, cada columna (por ese orden) es como si constituyera su propio índice. Esto significa que en el ejemplo anterior, no haría falta crear otro INDEX ID (id) ya que podríamos usar nuestro INDEX USUARIO simplemente con la cláusula SELECT ... WHERE ID = X;
Puedes ver si tu llamada sql usa o no los índices correctos anteponiendo a select la orden explain:
EXPLAIN SELECT * FROM mitable WHERE ....
Y para ser sinceros, usando explain para comprobar el uso de indices en distintos selects con indices multicolumna, he obtenido resultados poco consistentes con la 'regla de la izquierda' ya que en muchos casos parece que se usaban indices que teóricamente no debian estar disponibles ... posiblemente un caso de mala configuracion en mi tabla-test.

Tipos de indice
 
En algunas bases de datos existen diferencias entre KEY e INDEX. No así en MySQL donde son sinónimos.
Un índice que sí es especial es el llamado PRIMARY KEY. Se trata de un índice diseñado para consultas especialmente rápidas. Todos sus campos deben ser UNICOS y no admite NULL

Un indice UNIQUE es aquel que no permite almacenar dos valores iguales.

Los indices FULL TEXT permiten realizar búsquedas de palabras. Puedes crear indices FULLTEXT sobre columnas tipo CHAR, VARCHAR o TEXT.

Una vez creado puedes hacer búsquedas del tipo:
SELECT * FROM nombre_tabla WHERE MATCH(nombre_indice_fulltext) AGAINST('palabra_a_buscar');
Algunas limitaciones de los indices fulltext: solo busca por palabras completas. indice no encontrará indices. No se indexan las palabras de menos de cuatro letras. No se indexan columnas que contengan menos de tres filas, ni palabras que aparezcan en la mitad o mas de las filas. Las palabras separadas por guiones se cuentan como dos palabras.
Los indices ordinarios no tienen restricciones en cuanto a la existencia de valores idénticos o nulos. Una posibilidad interesante, si pensamos crear un índice sobre columnas CHAR y VARCHAR es la de limitar el campo a indexar. Por ejemplo, cada entrada en la columna puede ser de hasta 40 caracteres y nosotros indexar unicamente los primeros 10 de cada una. Para crear estos índices basta con indicar entre paréntesis el numero de caracteres a indexar despues del nombre de la columna:
ALTER TABLE libros ADD INDEX idx_autor(nombre(10), apellidos(10));

Desventajas de los indices

Los índices se actualizan cada vez que se modifica la columna o columnas que utiliza. Por ello no es aconsejable usar como indices columnas en las que serán frecuentes operaciones de escritura (INSERT, UPDATE, DELETE).

Tampoco tendría sentido crear indices sobre columnas cuando cualquier select sobre ellos va a devolver una gran cantidad de resultados; por ejemplo una columna booleana que admita los valores Y/N. En fin, tampoco es necesario usar indices en tablas demasiado pequeñas, ya que en estos casos no hay ganancia de rapidez frente a una consulta normal.
Finalmente, los índices ocupan espacio. A veces, incluso mas que la tabla de datos.

Creación de índices

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 NUL):

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));






Eliminar o cambiar un índice 


Algunas veces tendremos la necesidad de cambiar o eliminar un índice. 
Cuando hagamos algún cambio en el índice, necesitamos eliminar primero el índice 
y entonces reconstruirlo con la nueva definición. 

Para eliminar un 
índice de clave primaria podemos usar la siguiente sintaxis: 
ALTER TABLE nombreTabla DROP PRIMARY KEY;

Para eliminar un índice ordinario, único, o de texto completo, necesitamos 
especificar el nombre del índice y usar esta sintaxis: 
ALTER TABLE nombreTabla DROP INDEX nombreIndice;

También es válida esta otra sintaxis: 
DROP INDEX nombreIndice ON nombreTabla;

Si no estamos seguros de cuál es el nombre del índice que deseamos 
eliminar, podemos hacer uso de la sentencia SHOW KEYS: 
SHOW KEYS FROM nombreTabla;

Este es un ejemplo. 
CREATE TABLE usuarios
(
   id INT NOT,
   nombre VARCHAR(50) NOT NULL,
   apellidos VARCHAR(70) NOT NULL,
   PRIMARY KEY (id),
   INDEX (nombre, apellidos)
);

Veamos los índices que existen en esta tabla: 
mysql> SHOW KEYS FROM usuarios;
+----------+------------+----------+--------------+-------------+ 
| Table    | Non_unique | Key_name | Seq_in_index | Column_name |  
+----------+------------+----------+--------------+-------------+  .
| usuarios |          0 | PRIMARY  |            1 | id          |  .
| usuarios |          1 | nombre   |            1 | nombre      |  .
| usuarios |          1 | nombre   |            2 | apellidos   | 
+----------+------------+----------+--------------+-------------+ 

3 rows in set (0.00 sec)

La tercera columna es la que nos proporciona los nombres de los índices. 
Podemos observar que al no especificar un nombre al índice ordinario en (nombre, 
apellidos), se le ha asignado el nombre de la primera columna que forma el 
índice. 

A continuación vamos a eliminar los dos índices que existen en 
esta tabla: 
mysql> ALTER TABLE usuarios DROP PRIMARY KEY;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE usuarios DROP INDEX nombre;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

Por último, podemos verificar que estos índices ya no existen: 
mysql> SHOW KEYS FROM usuarios;
Empty set (0.00 sec)

Usando índices efectivamente 


Dado que los índices hacen que las consultas se ejecuten más rápido, 
podemos estar incitados a indexar todas las columnas de nuestras tablas. Sin 
embargo, lo que tenemos que saber es que el usar índices tiene un precio. Cada 
vez que hacemos un INSERT, UPDATE, REPLACE, o DELETE sobre una tabla, MySQL 
tiene que actualizar cualquier índice en la tabla para reflejar los cambios en 
los datos. 

¿Así que, cómo decidimos usar índices o no?. La respuesta es 
"depende". De manera simple, depende que tipo de consultas ejecutamos y que tan 
frecuentemente lo hacemos, aunque realmente depende de muchas otras cosas. 


La razón para tener un índice en una columna es para permitirle a MySQL 
que ejecute las búsquedas tan rápido como sea posible (y evitar los escaneos 
completos de tablas). Podemos pensar que un índice contiene una entrada por cada 
valor único en la columna. En el índice, MySQL debe contar cualquier valor 
duplicado. Estos valores duplicados decrementan la eficiencia y la utilidad del 
índice. 

Así que antes de indexar una columna, debemos considerar que 
porcentaje de entradas en la tabla son duplicadas. Si el porcentaje es demasiado 
alto, seguramente no veremos alguna mejora con el uso de un índice. 

Otra 
cosa a considerar es qué tan frecuentemente los índices serán usados. MySQL 
puede usar un índice para una columna en particular únicamente si dicha columna 
aparece en la cláusula WHERE en una consulta. 

Si muy rara vez usamos una 
columna en una cláusula WHERE, seguramente no tiene mucha sentido indexar dicha 
columna. De esta manera, probablemente sea más eficiente sufrir el escaneo 
completo de la tabla las raras ocasiones en que se use esta columna en una 
consulta, que estar actualizando el índice cada vez que cambien los datos de la 
tabla. 

Ante la duda, no tenemos otra alternativa que probar. Siempre 
podemos ejecutar algunas pruebas sobre los datos de nuestras tablas con y sin 
índices para ver como obtenemos los resultados más rápidamente. Lo único a 
considerar es que las pruebas sean lo más realistas posibles.