Moving Beyond MySQL - High End Database Solutions

These days everyone's building database driven Websites. A few years ago this was the main luxury of search engines, portals and the sites of large companies like Microsoft. But now, as the popularity of MySQL grows, database driven sites have become mainstream. MySQL offers many features, it's fast and it's free. However, what happens when you reach the limit of this software and have tried every little optimization and tweak?

Do you give up and resist growth? No, you migrate. MySQL isn't the only database system out there. You actually have many different systems to choose from - systems that have their own strengths and weaknesses, and which offer a number of advanced features that aren't available in MySQL. These features include Stored Procedures, Triggers, Atomic Updates, and Foreign Key Integrity, and they're the kinds of features that provide the degree of scalability required for quick searches on search engines and other large sites.

Not all of these solutions are costly - some are even free. And the more expensive solutions like Oracle and Microsoft SQL Server can be provided by hosting companies, which also brings down the cost and complexity of their maintenance. That's one great advantage of MySQL though: because of its simplicity anyone can maintain it. Many of these other servers require the attention of a full time, trained DataBase Administrator (DBA), but by using a hosted solution, you can lower this cost significantly.

A Little SQL Background

SQL (ess-que-el) is a standard for data storage. SQL is an acronym for Structured Query Language, and this language can be used to access data from databases and file systems. SQL is based on another similar language called Sequel. This has created some confusion in the development community, as many believe that SQL is a simple shortening of the older language's name.

Sequel was created in the mid-1970s by researchers at an IBM facility in the area of San Jose, California. This work was based on a paper published by Dr. E. F. Codd in June 1970 called "A Relational Model of Data for Large Shared Databanks".

IBM further developed a Research Database and API called by the same name of Sequel. By the late 1970s this work had progressed into a product called SQL/DS RDBMS. This new system was based on what would become the SQL standard, and was envisioned to make data access universal.

When SQL/DS was announced, many competitors rushed similar products to market using the same SQL standard, and IBM was beaten to market by an unknown company. A small company called Relational Software Inc. was the first to launch - and once its software became successful, the company changed its name to Oracle Corporation. To this day, Oracle has dominated the RDBMS market with its products. But more about Oracle later.

Even though each of these products is based around the SQL standard, each implements certain features differently, and as such, some things are very different from package to package (sound familiar?). They all cover the basics of the DDL (Data Definition Language) and DML (Data Manipulation Language). However, the main point at which they differ is in their optimization features and the DCL (Data Control Language). This is good news for you, because the basic terminology is the same: your basic queries and table markup will be almost the same across the different servers (most people don't use the DCL subset very much in everyday development).

Common Terms and Features

Let me explain what some of these features are and how they can improve your performance.

• Stored Procedures - These are bits of code that are used to modify the results from a query, or to modify data in a table. Unlike a query in your script, these are stored in a compiled format within the database itself, which allows for faster execution and response times. Each of these databases comes with a procedural language outside SQL that allows you to write and run stored procedures. This saves time over a normal query, as the RDBMS doesn't have to figure out how to run the query, it just has to execute it. It also enables you to add features and fix bugs with little or no change to the front end code.
• Triggers - These are specialized Stored Procedures. Triggers are functions that are automatically run by the database server in response to events you define, such as adding or modifying records in a table.
• Atomic Updates - These are commonly called "Transactions". What this allows you to do is to collect a series of queries into one group so that they're treated as a single entity. This way, if one query fails or is cancelled, the rest are reversed - which prevents partial transactions from being entered into your system. Atomic Updates are necessary for OnLine Transaction Processing, and in the maintenance of both your business's, and the customer's financial security.
• Foreign Keys - These enforce the relations in a relational database. Foreign Keys allow you to make sure that the data is valid without having to double-check against code, or worry about duplicate entries. These keys are enforced using the existing indices and table querying methods in the server, which makes them very fast and dependable.
• Views - These are special tables. With a View you can create the appearance of a table using standard SQL queries. This allows you to do joins across tables without incurring a performance hit when indices are checked and rows matched. A View maintains all these matches for you so they're available when you need them. Views are also good for the provision of subsets of data to different people. For instance, Views can allow you to make sure that your support staff never see a customer's credit card information - as far as the View is concerned, that information simply doesn't exist.

And now, on to the solutions!