4 ms·
> 3. It's performance is predictable. 99% of issues > with performance are easily solved with the right index This is one of my least favorite things about
by JohnBooty 12y ago
> 3. It's performance is predictable. 99% of issues
> with performance are easily solved with the right index
This is one of my least favorite things about MySQL. The "explain" feature is much less informative than that offered by MSSQL or Postgres, so figuring out what indexes or tuning to apply is harder than it needs to be.
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.
- morgo 12y agoIf 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-sql-queries-for-better-performance-webinar 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-of-mysql-575.html 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-project/ http://mysqlserverteam.com/the-mysql-optimizer-cost-model-pr...
- JohnBooty 12y agoThat'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.
- sevenproxies 12y ago>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&vq=covering&pg=PA177 https://books.google.co.uk/books?id=IagfgRiKWd4C&lpg=PA178&v...
- JohnBooty 12y agoWow, that's great news. Thanks for the correction!