I recently needed to produce a system for a client that owns a trading company. Now this is a fairly small business, but the data requirements were such that the database would easily grow above 4GB within year or two. So SQLExpress was out, as it is limited to 4GB. And the company is too small to afford the high fees of buying a full SQL Server.
So I spotted the opportunity to learn a bit, and downloaded and installed MySQL 5.1. First impressions were really positive. It installed withut hassle, and I found a tool called MySQL Workbench on the same site, which looked like a good management tool. I installed that too, and the program seemed OK to work with, I could design my tables etc. But then when I wanted to load those tables into a database I ran into some annoying limitations in the free version of MySQL workbench. Basically it is very clunky unless you buy the full version. Which I was not going to do for one little project.
Then I looked around and found Toad for MySQL. I was astounded. What an amazing product! It is completely free, and delivers an excellent user experience. You can write and run queries from it (with code completion!), you can design tables in a really friendly table editor which allows you to commit entities to the database ot to get the DDL to create them yourself, you can create table diagrams etc. I was suitably impressed.
So what is it like?
Initial impressions were quite favourable. There are a few concepts that you have to learn while designing tables, like the storage engines. Basically MySQL allows you to specify a storage engine per table. The InnoDB engine is transactional and allows foreign key relationships etc. The MyISAM engine is non-transactional, does not allow keys but it is super quick. Useful for logging or, in my case, useful for storing huge numbers of stock price records. There is also an in-memory table which works like a SQL Server temporary table, except that the in-memory tables are still defined after a reboot, without any data content, while a temp table is gone after a restart.
It’s query language is full featured, and a little foray of mine into date processing left me with a very favourable experience – there is lots of support online, and the language is as powerful as TSQL, as relates to dates at least. It was stable and fast, and I would be quite happy putting any of my systems into production using MySQL. Of course I am stating the obvious here – many huge systems (including Google) run on MySQL, but it is still quite nice to have a look for yourself.
And what didn’t I like:
It does not seem to have Windows Authentication support, which is a bit annoying. The Entity Framework did not work with an out the box client install, although I did read that it works, there is just some additional installation necessary. It now does support stored procedures, but still not all the richness we have in SQL server like DDL triggers, user defined functions etc.
And the conclusion?
For systems not hugely database bound but rather with an intelligent middle tier and data layer, I would be very happy to use MySQL. I believe it would be as productive as using SQL Server, and it doesn’t cost a cent! What a pleasure.