If you do not like explain, may I suggest using `EXPLAIN FORMAT=JSON` - it is new to MySQL 5.6, and contains additional meta data. There is also optimizer trace, which provides significantly more meta data. Some good examples in these slides: http://www.slideshare.net/oysteing/how-to-analyze-and-tune-s...
I believe your example might be http://mysql-nordic.blogspot.ca/2015/01/with-latest-verions-... - Fixed in 5.7.5.
There has been a lot of work in improving the optimizer recently. One project that I find particularly interesting, is the focus on improving the cost model: http://mysqlserverteam.com/the-mysql-optimizer-cost-model-pr...
That's great to see. Thank you for the correction. I did not know that information was available. I wonder if there are others like me who didn't know this because they've been doing the plain-vanilla EXPLAIN like always.
Re:MySQL 5.7.6 is out
by sevenproxies on Tuesday March 10, 2015 @12:17PM
(#9178534)
>why can't "select A,B from foo" be served directly from the index
mysql supports that with covering indexes.
https://books.google.co.uk/books?id=IagfgRiKWd4C&lpg=PA178&v...
And then when you do have everything indexed correctly, MySQL is significantly dumber (edit: looks like I'm significantly dumber! see correction below) about using those indexes than MSSQL or Postgres.
For example: those two can do index-only SELECTS. If table "foo" has columns A, B, C, D, E, and F and I've created an index on A and B, why can't "select A,B from foo" be served directly from the index? Postgres and MSSQL do it, and it's a huge performance boost in those two.
Postgres also lets you do some amazing things with partial indexes, indexes on computed values, etc.
MySQL might not be horrible here, but out of the 3 major relational databases I've used, it's clearly the weakest at this so it's funny to hear it touted as a strength.
Edit: Good news - I was wrong; MySQL does support index-only selects (covering indexes). User morgo also pointed out the improved JSON format for explain as well.