SEO Explorer Blog:
Try us:

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:

MySQL index with cardinality of 1

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)

MySQL index with normal cardinality

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.

0 0 vote
Article Rating
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x
Share via
Copy link
Powered by Social Snap