How to fix ERROR 1176: Key doesn't exist in table in MySQL

SHOW INDEXES FROM your_table_name;

SHOW KEYS FROM your_table_name;

-- Wrong - index doesn't exist
SELECT * FROM users FORCE INDEX (idx_nonexistent) WHERE id = 1;

-- Correct - remove FORCE INDEX if index doesn't exist
SELECT * FROM users WHERE id = 1;

-- Or use the correct index name
SHOW INDEXES FROM users; -- Check actual index names first
SELECT * FROM users FORCE INDEX (idx_email) WHERE email = '[email protected]';

-- Check which indexes exist
SHOW INDEXES FROM your_table_name;

-- Then drop only if it exists and use exact name
DROP INDEX idx_email ON users;

-- Or use safe syntax that won't error if missing
ALTER TABLE users DROP INDEX idx_email;

-- First, see the exact constraint name
SHOW CREATE TABLE your_table_name;

-- Look for CONSTRAINT `constraint_name` in the output, then use it:
ALTER TABLE your_table_name DROP FOREIGN KEY constraint_name;

-- Example
SHOW CREATE TABLE orders;
-- Output shows: CONSTRAINT `fk_customer_id` FOREIGN KEY...
ALTER TABLE orders DROP FOREIGN KEY fk_customer_id;

-- Check exact case of index names
SHOW INDEXES FROM users;

-- Use exact case when referencing
SELECT * FROM users FORCE INDEX (idx_email_lowercase) WHERE email = '[email protected]';

-- Not this (different case):
SELECT * FROM users FORCE INDEX (IDX_EMAIL_LOWERCASE) WHERE email = '[email protected]';

-- Create a single-column index
CREATE INDEX idx_email ON users(email);

-- Create a composite index
CREATE INDEX idx_user_status ON users(user_id, status);

-- Create a unique index
CREATE UNIQUE INDEX idx_unique_email ON users(email);

-- Create a fulltext index (for text searches)
CREATE FULLTEXT INDEX idx_description ON products(description);