DROP INDEX

DROP INDEX elimina un índice de una tabla. Eliminar índices innecesarios libera espacio en disco y mejora el rendimiento de las escrituras (INSERT, UPDATE, DELETE), ya que MySQL deja de mantener ese índice.

Sintaxis

-- Con DROP INDEX
DROP INDEX nombre_indice ON tabla;
 
-- Con ALTER TABLE
ALTER TABLE tabla DROP INDEX nombre_indice;
 
-- Ambas son equivalentes

Ejemplo básico

-- Crear un índice
CREATE INDEX idx_precio ON productos (precio);
 
-- Verificar que existe
SHOW INDEXES FROM productos WHERE Key_name = 'idx_precio';

Table	Non_unique	Key_name	Column_name	Index_type
productos	1	idx_precio	precio	BTREE

-- Eliminar el índice
DROP INDEX idx_precio ON productos;
 
-- Verificar que se eliminó
SHOW INDEXES FROM productos WHERE Key_name = 'idx_precio';

La consulta no devuelve resultados porque el índice ya no existe.

Eliminar clave primaria

La clave primaria se elimina con una sintaxis especial:

-- Crear tabla de ejemplo
CREATE TABLE test_pk (
    id INT PRIMARY KEY,
    dato VARCHAR(100)
);
 
-- Eliminar la PRIMARY KEY
ALTER TABLE test_pk DROP PRIMARY KEY;

No puedes eliminar la clave primaria si la columna es AUTO_INCREMENT. Primero debes quitar el AUTO_INCREMENT:

CREATE TABLE test_auto (
    id INT AUTO_INCREMENT PRIMARY KEY,
    dato VARCHAR(100)
);
 
-- Esto falla:
-- ALTER TABLE test_auto DROP PRIMARY KEY;
-- Error: Incorrect table definition; there can be only one auto column and it must be defined as a key
 
-- Primero quitar AUTO_INCREMENT
ALTER TABLE test_auto MODIFY id INT NOT NULL;
 
-- Ahora sí se puede eliminar
ALTER TABLE test_auto DROP PRIMARY KEY;

Eliminar múltiples índices

Puedes eliminar varios índices en una sola sentencia ALTER TABLE:

-- Crear varios índices
CREATE INDEX idx_a ON productos (precio);
CREATE INDEX idx_b ON productos (stock);
CREATE INDEX idx_c ON productos (nombre(20));
 
-- Eliminar todos a la vez
ALTER TABLE productos
    DROP INDEX idx_a,
    DROP INDEX idx_b,
    DROP INDEX idx_c;

Eliminar múltiples índices en una sola sentencia es más eficiente que hacerlo por separado porque MySQL solo reconstruye la tabla una vez.

Verificar antes de eliminar

Antes de eliminar un índice, verifica que no se usa en consultas importantes:

-- 1. Ver qué consultas podrían usar el índice
-- Buscar en el slow query log o performance_schema
 
-- 2. Probar con índice invisible primero (MySQL 8.0+)
ALTER TABLE productos ALTER INDEX categoria_id INVISIBLE;
 
-- 3. Ejecutar las consultas habituales y verificar rendimiento
 
-- 4. Si todo va bien, eliminar
-- DROP INDEX categoria_id ON productos;
 
-- 4 alternativo. Si hay problemas, restaurar
ALTER TABLE productos ALTER INDEX categoria_id VISIBLE;

Eliminar índice UNIQUE

Al eliminar un índice único, también se elimina la restricción de unicidad:

CREATE TABLE test_unique (
    id INT AUTO_INCREMENT PRIMARY KEY,
    codigo VARCHAR(50),
    UNIQUE INDEX uq_codigo (codigo)
);
 
INSERT INTO test_unique (codigo) VALUES ('A001');
 
-- Antes: no permite duplicados
-- INSERT INTO test_unique (codigo) VALUES ('A001');
-- Error: Duplicate entry
 
-- Eliminar el índice único
DROP INDEX uq_codigo ON test_unique;
 
-- Ahora sí permite duplicados
INSERT INTO test_unique (codigo) VALUES ('A001');
 
SELECT * FROM test_unique;

id	codigo
1	A001
3	A001

Cuándo eliminar índices

Señales de que un índice es innecesario:

Nunca aparece en la columna key de EXPLAIN para tus consultas habituales
Es un duplicado de otro índice (un índice (A, B) ya cubre consultas sobre (A))
La tabla es muy pequeña y las consultas no se benefician del índice
La columna tiene cardinalidad muy baja

-- Encontrar índices potencialmente duplicados
-- Un índice (A) es redundante si existe un índice (A, B)
SELECT
    s1.TABLE_NAME,
    s1.INDEX_NAME AS indice_corto,
    s2.INDEX_NAME AS indice_largo
FROM information_schema.STATISTICS s1
JOIN information_schema.STATISTICS s2
    ON s1.TABLE_SCHEMA = s2.TABLE_SCHEMA
    AND s1.TABLE_NAME = s2.TABLE_NAME
    AND s1.COLUMN_NAME = s2.COLUMN_NAME
    AND s1.SEQ_IN_INDEX = 1
    AND s2.SEQ_IN_INDEX = 1
    AND s1.INDEX_NAME != s2.INDEX_NAME
WHERE s1.TABLE_SCHEMA = 'tienda_mysql';

IF EXISTS

Para evitar errores si el índice no existe:

-- Esto falla si el índice no existe
-- DROP INDEX idx_inexistente ON productos;
-- Error: Can't DROP 'idx_inexistente'; check that column/key exists
 
-- Opción segura con ALTER TABLE (MySQL no tiene DROP INDEX IF EXISTS)
-- Verificar primero:
SELECT COUNT(*) AS existe
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'tienda_mysql'
  AND TABLE_NAME = 'productos'
  AND INDEX_NAME = 'idx_inexistente';

existe
0

Limpieza

DROP TABLE IF EXISTS test_pk;
DROP TABLE IF EXISTS test_auto;
DROP TABLE IF EXISTS test_unique;

En el siguiente artículo veremos FORCE INDEX y los hints para controlar qué índice usa MySQL.

Escrito por Eduardo Lázaro