4 ms·

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

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.

plasma 5y ago
You could perhaps fix it by trying to use some index hints in the query to get a bit more consistency, see https://dev.mysql.com/doc/refman/8.0/en/index-hints.html https://dev.mysql.com/doc/refman/8.0/en/index-hints.html
- radu_floricica 5y ago
  Yeah, that's what I mean by "trying to do the optimizer's job". But this is meant for edge cases - doing this fully would mean starting to rewrite the optimizer in the app. This is where using the "explain" might help - if it's choosing what seems like a sane index and has a limited range of rows, I send the query "as is". If it fucks up and does a table scan, I'll force a compromise index.
  - throwdbaaway 5y ago
    > Generally in MySQL we send queries massaged to a point where optimizer doesn’t have to think about anything. https://dom.as/2015/07/30/on-order-by-optimization/ https://dom.as/2015/07/30/on-order-by-optimization/ - Wisdom from the guy who managed to get MySQL to work at facebook scale.
    - radu_floricica 5y ago
      Thank you
mst 5y ago
Most of the people annoyed with PostgreSQL in these comments are annoyed because it feels like you have to write your own replication and connection management Most of the people annoyed with MySQL in these comments are annoyed because it feels like you have to write your own optimiser Welcome to software, I guess :D

plasma 5y ago

You could perhaps fix it by trying to use some index hints in the query to get a bit more consistency, see https://dev.mysql.com/doc/refman/8.0/en/index-hints.html https://dev.mysql.com/doc/refman/8.0/en/index-hints.html

radu_floricica 5y ago
Yeah, that's what I mean by "trying to do the optimizer's job". But this is meant for edge cases - doing this fully would mean starting to rewrite the optimizer in the app. This is where using the "explain" might help - if it's choosing what seems like a sane index and has a limited range of rows, I send the query "as is". If it fucks up and does a table scan, I'll force a compromise index.
- throwdbaaway 5y ago
  > Generally in MySQL we send queries massaged to a point where optimizer doesn’t have to think about anything. https://dom.as/2015/07/30/on-order-by-optimization/ https://dom.as/2015/07/30/on-order-by-optimization/ - Wisdom from the guy who managed to get MySQL to work at facebook scale.
  - radu_floricica 5y ago
    Thank you

mst 5y ago

Most of the people annoyed with PostgreSQL in these comments are annoyed because it feels like you have to write your own replication and connection management Most of the people annoyed with MySQL in these comments are annoyed because it feels like you have to write your own optimiser Welcome to software, I guess :D