¿Cómo agrego índices a las tablas MySQL?
Tengo una tabla MySQL muy grande con aproximadamente 150.000 filas de datos. Actualmente, cuando intento ejecutar
SELECT * FROM table WHERE id = '1';
el código funciona bien ya que el campo ID es el índice principal. Sin embargo, por un desarrollo reciente en el proyecto, tengo que buscar en la base de datos por otro campo. Por ejemplo:
SELECT * FROM table WHERE product_id = '1';
Este campo no estaba indexado anteriormente; sin embargo, agregué uno, por lo que MySQL ahora indexa el campo, pero cuando intento ejecutar la consulta anterior, se ejecuta muy lentamente. Una consulta EXPLAIN revela que no hay ningún índice para el campo product_id cuando ya agregué uno y, como resultado, la consulta tarda entre 20 y 30 minutos en devolver una sola fila.
Mis resultados completos de EXPLAIN son:
| id | select_type | table | type | possible_keys| key | key_len | ref | rows | Extra |
+----+-------------+-------+------+--------------+------+---------+------+-------+------------------+
| 1 | SIMPLE | table | ALL | NULL | NULL | NULL | NULL |157211 | Using where |
+----+-------------+-------+------+--------------+------+---------+------+-------+------------------+
Puede resultar útil tener en cuenta que acabo de echar un vistazo y que el campo ID está almacenado como INT mientras que el campo PRODUCT_ID está almacenado como VARCHAR. ¿Podría ser esta la fuente del problema?
ALTER TABLE `table` ADD INDEX `product_id_index` (`product_id`)
Nunca comparar integercon stringsMySQL. Si ides así int, elimine las comillas.
ALTER TABLE TABLE_NAME ADD INDEX (COLUMN_NAME);
Puede utilizar esta sintaxis para agregar un índice y controlar el tipo de índice (HASH o BTREE).
create index your_index_name on your_table_name(your_column_name) using HASH;
o
create index your_index_name on your_table_name(your_column_name) using BTREE;
Puede conocer las diferencias entre los índices BTREE y HASH aquí: http://dev.mysql.com/doc/refman/5.5/en/index-btree-hash.html
Se pueden agregar índices de dos tipos: cuando defines una clave primaria, MySQL la tomará como índice por defecto.
Explicación
Clave primaria como índice
Considere que tiene una tbl_studenttabla y desea student_idcomo clave principal:
ALTER TABLE `tbl_student` ADD PRIMARY KEY (`student_id`)
La declaración anterior agrega una clave principal, lo que significa que los valores indexados deben ser únicos y no pueden ser NULL.
Especificar el nombre del índice
ALTER TABLE `tbl_student` ADD INDEX student_index (`student_id`)
La declaración anterior creará un índice ordinario con student_indexnombre.
Crear índice único
ALTER TABLE `tbl_student` ADD UNIQUE student_unique_index (`student_id`)
Aquí, student_unique_indexse asigna el nombre del índice a Student_id y se crea un índice para el cual los valores deben ser únicos (aquí se puede aceptar nulo).
Opción de texto completo
ALTER TABLE `tbl_student` ADD FULLTEXT student_fulltext_index (`student_id`)
La declaración anterior creará el nombre del índice de texto completo student_fulltext_index, para lo cual necesita MyISAM Mysql Engine.
¿Cómo eliminar índices?
DROP INDEX `student_index` ON `tbl_student`
¿Cómo comprobar los índices disponibles?
SHOW INDEX FROM `tbl_student`
