Como hacer una pagina web y ganar dinero
Creada para aprender lo que mas nesesitas porque la calidad es mejor que la cantidad.
Blog

Optimiza tu MySQL usando Indices

Tags: indice, php, mysql
publicado el 2011-10-14   leido 1418 leídas

¿Que es un índice?


Un índice (o KEY, o INDEX) son 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 asi 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 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.


Te gust?

No repetir registros duplicados en Mysql

Eliminar registros duplicados en MySQL

 
Información
Christian Valencia publicado porPor: Ero-Fierce

Suscribete
Recibe los ultimos articulos en tu email:


Ultimos Artculos
Hacer SEO en el 2016 - 2017
Habilitar sonido al pulsar las teclas Bloq Mayús, Bloq Num y Bloq Des
10 trucos viejos, seo que nunca te dijeron
15 Efectos carrusel como pagina de libro animado con ejemplos
No se pudo generar una instancia de usuario de SQL Server debido a un error (.mdf
Solucion Error 26. No se encontró el servidor o éste no estaba accesible. Sql

Todos los tutoriales

Hacer una web paso a paso Php y MySQL Javascript
Posicionamiento Web SEO Cómo ganar dinero con tu web Herramientas y Programas Web
Maquetación con CSS Hosting y Dominios MySQL y Phpmyadmin
Ajax UML: Analisis y Diseño Programacion Orientada a Objetos
Java
 
Copyright © 2009 - pagina Web 2 - Todos los derechos Reservados