Indices en Base de Datos en MySQL (MariaDB)

Buenas noches queridos lectores, el día de hoy veremos como crear índices en base de datos, para eso debemos saber que es un índice, de manera general, y ya después haremos lo que nos gusta e interesa que es crear el índice. Como les comentaba veamos que es un índice en base de datos (BD).

Segun la Wikipedia, un índice es:

Lista o relación ordenada con algún criterio de libros, documentos, monedas, objetos en venta, etc., de una persona, empresa o institución, que generalmente contiene una breve descripción del objeto relacionado y ciertos datos de interés.

Esa definición es la de un índice en general, el índice en una base de datos es algo similar, nos permite realizar consultas de una manera más rápida y funcional. Vemos la definición de Wikipedia de Índices en Base de Datos

El índice de una base de datos es una estructura de datos que mejora la velocidad de las operaciones, por medio de identificador único de cada fila de una tabla, permitiendo un rápido acceso a los registros de una tabla en una base de datos.

Los índices nos ayudan a mejorar la velocidad de las consultas, tal vez en una tabla sin índices que tengan pocos datos no se notará la diferencia, pero imaginemos una base de datos inmensa, vamos a tomar de ejemplo la base de datos de resultados deportivos que estamos realizando, e imaginemos que tenemos millones de partidos, solo queremos los resultados del equipo de Cruz Azul, en una fecha determinada, por defecto, al crear las llaves primarias, se crea un índice, ya que una llave primaria en sí, es un índice, veamos la estructura de la tabla partidos.

Como vemos, todos los campos que son keys, por default son índices, pero que pasa, si tenems dados de alta millones de partidos y solo queremos los partidos de Cruz Azul en un rango de fechas, pues el programa buscará partido por partido, y excluirá las fechas que no entren en el rango, ésto porque el índice es el idequipolocal, idequipovisitante, idpartido, etc.  pero no hay ningún índice con la fecha, pues bien, el índice nos servirá para eso, al crear un índice en la fecha, ademas de buscar por idequipolocal y idequipovisitante ademas buscará por fechas, ya no buscará partido por partido, sino que buscará ahora, ademas por fecha, que sería el índice y se iría automáticamente a la fecha que le pusimos dentro del rango inicial, haciendo las consultas aún mas veloces, imagínense ahora que las consultas están unidas con uno o varios JOINS, puede tardar demasiado una consulta y por eso es necesario y útiles los índices.

Pero basta ya de teoría, vamos a lo que nos interesa y nos gusta que es hacer las cosas.

Para crear un índice utilizaremos la siguiente instrucción:

Ejercicio 1:

Crear un índice en la tabla PARTIDOS, en el campo fecha, para agilizar las consultas que tengan rangos de fechas, utilizando el comando CREATE INDEX

create index fecha on partidos (fecha);

Como pueden ver se utiliza el comando CREATE INDEX, después se le pone un nombre al índice, con la preposición ON le indicamos a que tabla aplicará el índice y entre paréntesis el campo o campos que se utilizarán de índice, así es, leyeron bien, un índice puede estar definido por varios campos y no uno solo, veamos un ejemplo de uso de índice

Ejercicio 2

Crear una consulta que muestre los partidos jugados por Cruz Azul en lo que va del año 2016

select * from partidos

where (idequipolocal =1 or idequipovisitante=1)

and fecha between '20160101' and '20160731'

Como ven en este ejemplo utilizamos el rango de fecha, de manera que será mucho más rápido la consulta, ya que la Base de Datos (BD) no buscará desde el idpartido, ya que empezaría desde el 1, sino que se irá automáticamente a la fecha que interesa, ignorando lo demás, una vez que encuentra la fecha inicial del rango de fechas, entonces ahora si filtrará por el equipo que queremos, pero como vemos en la tabla, hay campos donde se muestra explícitamente el nombre del equipo, así, que haremos el nombre del equipo también un índice.

Ejercicio 3

Crear dos índices en la tabla PARTIDOS, el primero hará referencia al EQUIPOLOCAL, y el otro a EQUIPOVISITANTE, utilizando la sentencia CREATE INDEX

create index equipo_local on partidos (equipolocal);

create index equipo_visitante on partidos (equipovisitante);

Como ven hemos creado dos índices, el primero hace referencia al campo equipolocal y el otro hace referencia al campo equipovisitante, podemos crear la siguiente consulta:

select * from partidos

where (equipolocal ='Cruz Azul' or equipovisitante='Cruz Azul')

and fecha between '20160101' and '20160731'

En este caso, nuestra consulta es aún mucho más rápida, ya que además de comenzar la búsqueda desde el rango de fechas, no buscará en todos los equipos, simplemente en los que sean Cruz Azul, en este caso, son muy pocos registros, sin embargo la diferencia si se alcanza a apreciar aunque se un poco.

Ejercicio 4

Realizar una consulta donde se muestren los partidos entre Guadalajara y Cruz Azul en el último año, los índices creados ayudarán a que la consulta sea más rápida

sselect p.fecha, p.equipolocal, p.equipovisitante, p.goleslocal, p.golesvisitante, e.nombre

from partidos p

INNER JOIN estadios e

on p.idestadio = e.idestadio

where (equipolocal ='Cruz Azul' and equipovisitante='Guadalajara')

or (equipolocal ='Guadalajara' and equipovisitante='Cruz Azul')

Pero, ¿como podremos saber que índices tenemos?, ahh, esa es una pregunta interesante, para saber los índices que tenemos en una determinada tabla utilizaremos la sentencia SHOW KEYS

Ejercicio 5

Mostrar la lista de índices que se encuentran creados en la tabla PARTIDOS utilizando la sentencia SHOW INDEX

show keys from partidos

Como pueden ver, ese comando nos muestra todos los índices creados en la tabla y así podríamos consultarlos, o borrarlos, según sea el caso, y ya que hablamos de borrar, ¿como borraríamos un índice?

drop index equipolocal_visitante_fecha on partidos

Como pueden ver, con las sentencia DROP INDEX podemos eliminar un índice.

Hasta aquí el tema de los índices, espero haya sido de su agrado, pero sobre todo de su utilidad, espero comenten, haga +1, compartan o den clic  algún anuncio, recuerden que cualquier duda, estoy a sus ordenes. Muchas gracias a todos