The MySQL family has been innovating rapidly. New features need names and sometimes those names are confusing. Describing something as multi-master or a NoSQL solution has confused me.
I have been working with a few customer cases and one interesting case popped up. The customer was facing a peculiar problem where the rows column in the EXPLAIN output of the query was totally off. The actual number of rows was 18 times more than the number of rows reported by MySQL in the output of EXPLAIN. Now this can be a real pain as MySQL uses “the number of rows” estimation to pick and choose indexes and it could really be picking up a wrong index simply because of the wrong estimate.
A few people have asked me how it’s going, so I thought I’d just share it with everyone. Things are going great. I’m writing much more quickly than I thought I would be, and as a result I’m finding I have time to do more changes than I thought I could, which makes me happy. I should be finished drafting the chapters by the end of the year.
I had some conversations with a few people at Oracle Open World about features that would be beneficial in InnoDB. They encouraged me to blog my thoughts, so I am.
Someday I’d like to have a clear mental list of features I want in MySQL in general, but that is a much harder list to organize and prioritize. InnoDB is an easier place to get started.
Everyone who started using MySQL before 5.5 started off with MyIsam. It was the default storage engine and you had to go out of your way to use anything else. It was a good looking database, rugged, simple and fast in many respects. But crash recovery was not a MyIsam forte. Neither was locking or transactions. Speed was pretty good on reads. But many database snobs pointed to the short comings of MyIsam and claimed it was proof that MySQL was a ‘toy database’.
I was looking into the history about why SYSDATE() and NOW() behave differently in MySQL, and it looks like in 4.0 and 4.1 they used to be the same. But as of MySQL 5.0, SYSDATE() was changed to emulate Oracle’s behavior, that is, it returns the time as of the function execution, not as of the statement start.
I will be at Zendcon next week with two presentations. What’s New with MySQL will be on Wednesday the 19th and The Care and Feeding of a MySQL Database on Thursday the 20th.
Other MySQL centric session are by Bill Karwin on SQL Injection Myths and Fallacies and MySQL 5.5 InnoDB Tuning. Plus Ligaya Turmelle will present Character Sets Suck.
I am looking for a few more unique examples to add to the final chapter of my upcoming book on MySQL Backup & Recovery. If you would like to share your fun experience, receive a mention and a free copy please let me know via comment. If you would like to share but not have your comment published, please note at top of your feedback.
Thanks for helping to contribute to a detailed list of what could go wrong and how to be prepared for a MySQL disaster.