MySQL Shortcomings

I have been using MySQL with ASP .NET at work for about the last 3 years now.

I've been stewing on this for quite some time, hoping MySQL would release changes and fixes. As it stands, I cannot recommend MySQL's use to anyone. Even simple sites have a way of becoming complex over time, so don't think you won't eventually run into these very serious problems.

As of this writing, I'm using 5.0 as it's what comes packaged with our linux system. The current version is 5.1. However, these problems or deficiencies are on both versions.

Here are the main issues that I run into, which are ridiculous oversights:

• (Update 2011.03.15) Use of temporary tables breaks replication (!!!). Reference link.
• (Update 2011.02.02) No Check constraints (!!!). Reference link.
• (Update 2011.02.02) No Full Outer join (!!!). Reference link.
• Foreign key cascades do not fire triggers (!!!). Reference link.
• You cannot disable triggers (!!!). Reference link.
• You can't use the same temp table twice in the same SQL statement (!!!). Reference Link.
• There is no method for using the results of stored procedures or functions as a result set for other queries (!!!):
• Can't create or insert into a temp table with sproc results: E.g. "create temporary table mytemptable call my_sproc();" doesn't work. Neither does "insert into mytemptable call my_sproc();"
• Can't use a stored procedure as a table alias: E.g. "select * from (call my_sproc());" doesn't work
• Can't create a view from stored procedure results: E.g. "create view myview as call my_sproc();" doesn't work
• There is no table type in MySQL, so functions can't return a table and also can't be used to pass result rows to other queries. E.g. "select * from my_func();" doesn't work
• These things are not explicitly stated in the manual, but questions regarding this kind of result reusability do come up from time to time. E.g. here.
• No array support. This means that you cannot pass an array to, nor receive an array from, a stored routine or function. Reference link.
• Stored functions can only return a single value. Not explicitly stated, but implicit in the stored function syntax. Reference link.
• Unintelligible error messages. Reference link (older but still indicative).
• No user-raisable error messages. Reference link.
• Integration with ASP: Entity Framework stored procedure inserts/updates/deletes are not configurable from the GUI -- they must be configured by hand. Reference link.

I can't even wrap my head around a company releasing a database and calling it production ready with some of these issues, particularly the first several half-dozen.

Note that there are work-arounds to nearly all of these issues, but they all entail a certain amount of extra programming, and configuration management (at least having to remember why you had to use those hacky, unclear SQL statements). If you use the database extensively, several of these problems can join forces to make the developer / admin experience very frustrating.

Personally, I will not choose MySQL 5.x again for any new projects. I'll even switch away from it on existing projects if given the opportunity. Hopefully MySQL 6 will be better.

So instead, I'll use SQL Server (even Express if I have to) when integrating with .NET applications. Otherwise, I'll use Postgres, which is free and open source. I'm sure those systems have their problems as well, but at least some of the more serious ones above are absent or mitigated.

Keyboard Layouts

As you may know, type-writers originally had more or less alphabetic key ordering. Typing was a mechanical process -- essentially pressing a key caused block letter to smack the paper, printing that letter. It was soon discovered that certain very common letter combinations (e.g. ST) would physically cause the machine to jam.

The QWERTY keyboard layout was devised by Christopher Sholes to minimize jamming. The commercial success of his typewriter in 1898, featuring the QWERTY layout, led many manufacturers to adopt it. Over time, it became the standard layout for keyboards, and it has changed only slightly since then. By and by computers came along, and the QWERTY layout has come to include some things specifically for the computer -- function keys, arrow keys, etc.

So a professor named Dvorak decides to investigate efficient text entry methods. After a bit of research on hand physiology and letter / diagraph frequencies, he came up with a keyboard layout specifically designed to minimize finger travel. It was patented in 1936 and is commonly known as the Dvorak keyboard. The layout, being an ANSI standard, is available but hidden away on most modern operating systems. The layout, once learned, promises faster data entry and reduced risk of CTS / RSI over the QWERTY layout. Of particular note is that 70% of English words can be typed using just the ten keys on the home row of the Dvorak keyboard.

However, the Dvorak keyboard never saw widespread acceptance. There are many cited reasons for this. Of particular note was the fact that workers would need retraining, and new equipment would have to be purchased (before the computer age). On top of that, you might recall that the 30s was a particularly bad time for the world economy. Even today, few studies on QWERTY vs. Dvorak exist, therefore it is difficult to empirically justify a switch from the established standard.

Part of the purpose of this entry was to explore the Dvorak keyboard, and in fact I wrote all of the above with a Dvorak-based layout, before editing. (I can see why people are reticent to switch... it is very frustrating to relearn to type!) However, I can't help but wonder if, after the training obstacle is overcome, if it won't be better, easier, less strain, maybe even faster. Perhaps I'll give it a try.

Regardless of what I do, I doubt that Dvorak will ever become the standard, even if it was one day empirically found to be superior. Learning to type is no small task (something you take for granted until you have to relearn it). The established labor pool has a QWERTY mindset. Heck even the staggered rows that modern keyboards have are owed to the "ancient" typewriter. It had to stagger them so that all the keys could mechanically connect to every corresponding letter block. Typing is such a fundamental function of computer use today, and QWERTY is so entrenched, that probably the only thing that will change it is to make the keyboard obsolete as a data entry method.