MySql Pleasantness

I've recently embarked on the learning curve of using MySql. The main reason for this is that a few of my clients haven't been able to justify the expense of Microsft SQL Server licences, and posed the question of "is there an open source database that can meet our demands?". I still use and love MSSQL, but not every project needs such a sophisticated database platform.

MySql was our first port of call. It seems like an actively developed and sturdy database platform. I've read good reports about it being used successfully in many performance demanding applications. It's also got some nice features out-of-the-box, such as replication, unicode support and nice developer tools. MySql 5.0 will deliver even more cool features, such as views, triggers, stored procedures and functions.

If you're a .NET developer who's considering getting into MySql, I'd certainly give it the thumbs up. It's been a pleasant expereince, and the community are very helpful.

I'd like to offer a few hints and resources for those who are entering the MySql world, because there's a few things that took me a while to get into.

1. Documentation
The MySql documentation is fab. I found it easy to navigate, and very usable from a developers perspective. Also, there's lots of comments provided by users that can really help you tackle tricky problems and gotchas. I wish the MS docs had this "user comments" feature. I exclusively use the online reference, but there's also downloadable CHM files and PDFs.

http://dev.mysql.com/doc/mysql/en/index.html

This was my starting point for learning MySql, and I certainly provided a nice way in.


2. Forums
Whenever I'm getting into some new component or application, I need to be able to ask
questions and get responses. The best place I've found for this is the forums. The MySql site is quite a big one, but you'll find a great set of public forums here:

http://forums.mysql.com/

Always search the forums first, seriously, there's a good chance you'll find a lot of people who've had similar problems. Also, once you start learning this stuff, give back by responding to other peoples issues - it's just good ettiquett.

3. Books
So far I've only got one book - "High Performance MySql" by O'Reilly. It's full of great hints and tips, and gives good background to how MySql is structured, how it derives query plans etc. I'm actully starting to feel like I'm understanding the MySql engine, despite only having worked with it for a few months. I'm also going to buy a book called "Pro MySql", but Jay Pipes. This guy has helped me out loads on the forums, and seems to really know what he's talking about. I imagine his book will be invaluable.

4. Some things I wish were different

The overall MySql experience has so far been a positive one, but there are a few things I miss from Sql Server.

- Query Plans. When you explain a query in MSSQL, you get lots of data about what's going on. In MySql, you get much less. I think that, as my understanding of the database increases, I'll know what does and doesn't perform in queries, but I do wish there was a little more info in those query explanations!

- Indexes. Currently, MySql 4 will only allow one index to be used per query. Well, it will use different indexes for subselects and union queries, but I like the fact that MSSQL will use more than one index for a single select statement. As it happens, there's tricks which allow you to still phrase queries so they perform, but it just means you can't always apply MSSQL knowledge to MySql.

- MySql Query Browser. This *is* a very nice tool. However, I'm just too used to MS Query Analyser ;-). One thing that QA users will want to do is make the query window full screen, which can be done using the F11 key. Also, I've not been able to find a way of executing just the selected text, which is a shame. Maybe I should work on an SqlBuddy for MySql :)

5. Things I like about MySql

- Server Administrator. This is a really good tool, and it allows you to tweak almost all settings for the server. Also, it has nice graphs showing Query Cache Usage, current conntections, and other health related issues. Editing my.ini in a text editor is quite easy too, since there's loads of comments.

- Multiple Engines. MySql has many different storage engines (such as InnoDb, MyISAM etc). At first, this makes it seem a little overwhelming, but I think I like the idea. Basically, you can select the optimal storage engine for your application. For example, many standard transaction processing apps would benefit from InnoDB, which has row level locking, referential integrity etc. More OLAP type applications might benefit from the MyISAM or up and coming Archive formats. You can also create In-Memory tables for super-fast data access.

- Cross Platform. This is an obvious one, but it's quite handy to be able to develop a database on my local windows box and then use the same scripts to create it on a Linux box.

- Easy Backup. The "dump" command allows you to instantly dump an entire database structure and contents to a text file. You can also just do individual tables.

Ok, I've rattled on enough here. Hopefully there's some useful insights to those developers who are considering using it.