Thoughtless or Very Thoughtful: MySQL On-disc Storage System

A discovery into how schema and data are stored on disc for various mysql storage engines will definitely evoke many varied opinions. For some it could mean flexibility or plug and play architecture. For some others it could just mean a lack of well thought out architecture. Here is a summary (click on the table below for better resolution):

Microsoft SQL Server to MySQL: Three important differences that matter.

I recently started working on MySQL after a long association with MS SQL Server for close to 10 years. I am sharing my experience from the initial days; my frustrations, my excitements and some real differences between the two DBMSs.

MySQL has a flexibe feel almost same as that of a programming language; somehow you get this instinctive confidence that you can find a work around for almost anything. There are lots of curious things in MySQL when compared with MS SQL server e.g. support for a zero date, eccentric stored-procedure syntax, enum data-types and so on. The three things that caught my explicit attention and that could be a deciding factor while designing applications are:

1. SQL server has mainly two kinds of tables: the tables and the temporary tables. Oh well, we have table variables, table data types, sparse wide tables, upcoming columnar tables etc. MySQL has Storage-Engines instead. When you create a table, you specify the storage engine that is used to create the table. For a regular OLTP application, INNODB engine supports row level locking, transactions etc. But then if you don’t need all that, you can go for Memory engine or MyISAM engine or CSV storage engine depending on your specific need. Gives you more control, flexibility, optimization and performance. InfiniDB is another MySQL storage engine that mainly has columnar storage, is developed by a third-party vendor and integrates well with MySQL Query language and processing engine. This open source thing is cool stuff. And this storage engine stuff is cooler. There is XtraDB storage engine, MariaDB storage engine and you can write and plug your own if you have a need and the skills. Explore more on MySQL storage engines at http://dev.mysql.com/doc/refman/5.5/en/storage-engines.html

2. Sad, there are no linked-servers in MySQL. Instead there is “Federated Storage Engine”, so you have to replicate the callee tables schema in the caller databases and you have to maintain the sync. But I could create a linked server from MS SQL Server 2008 R2 to MySql. MS SQL Server is definitely a few steps ahead here including support for OpenQuery feature. But then SSIS is a few steps behind in talking to MySQL servers.

3. MySQL has a FIND_IN_SET funtion. The FIND_IN_SET() function is optimized to use bit arithmetic and is used to store and query “multi-value” column data. A definite win over MS SQL Server. Can be a make and break decision for lots of applications.

During my initial part of the journey, I have been riding a sine wave. I feel good about few things and feel not so good about others. But I am happy that I have both the toys at hand to play with. There is no clear winner, but if we take out the cost factor, MS Sql Server has a more professional feel and more management tools. And if we bring in the cost factor, you get almost an equivalent DBMS that needs a little extra taming. Beauty before the age or the other way round? The jury is still out on this issue.