I have a moderately large table (hundreds of millions of records) with very simple structure but lots of columns and keys. This post is a huge "aha!" moment for me - I keep struggling with how the optimizer is choosing which index to use. It keeps making stupid mistakes and behaves differently from instance to instance - I still have a staging server where I just have to avoid an admin interface because it takes minutes to load, and I haven't invested more than a few hours to debug because it's not production.

I have pieces of half commented code where I do the optimizer's job and try to guess which index is best and chose it manually. Lately I've been considering doing an "explain" first and based on that tweak the query.

So yeah - I love MySQL, and I don't think I'll be able to invest the time to switch, but at least now everybody can see the emperor is naked and it wasn't just them. The optimizer sucks, including in MySQL 8.