Weird case of MySQL index that doesn’t function correctly

I created an index on one field, which the database was dumped for backup.

The operation took 5 hours, which is odd for the amount of data (156 million rows)

Queries not working as it should

I ran the new code using the new index, and it seems very slow—doing a select on the index took 30 seconds, which seems like a full table scan.

I did an explain on the query, and it showed that the plan would use the index.

I looked at the index data and saw the index cardinality as 1, another oddity:

Optimizing the table

When a computer has issues, the first thing you do is a reboot, in my toolbox, the first thing is to optimize the table, which took 30 minutes (remember that index generation took 5 hours)

Now the cardinality is 70728744 and select took just 0.2 seconds (still slow but better than 30 seconds)

Summary

The lessons are:

• Don’t do schema changes while running a database dump
• Check index cardinality, or if something is odd, optimize the table

Is it a bug? A feature? I have no idea.