advertisement

Print

YourSQL Database Might Just Be MySQL
Pages: 1, 2, 3

Things missing from MySQL

Speed always demands a performance price in most computer applications, and MySQL is no exception. Two of the most fundamental high-end database features missing from MySQL are transactions and stored procedures (including triggers). Both of these features, particularly transactions, require an amount of database server overhead that can noticeably impact performance. In the interests of speed and robustness, these features have been omitted. It's worth noting that some high-end database features other than transactions and stored procedures are also missing; for instance, Oracle's ability to calculate dates in Roman numerals.



Some other, more germane features that are missing in MySQL (and currently near the top of the TcX TODO list) are:

  • Subqueries (SELECT x FROM y WHERE z IN (SELECT a FROM b WHERE c=d)).
  • Server-side cursors.
  • Query caching (particularly for web applications).
  • Locking of databases.
  • Better join support (NATURAL JOIN, UNION, MINUS, INTERSECT and FULL OUTER JOIN).

Some of the missing features may be ones that you consider crucial -- if so, MySQL won't work for you. You may consider rearchitechting your application to leverage what MySQL can do. Or you may want to check out PostgreSQL. You may even need to grit your teeth and buy a high-end tool. But for the rest of us, MySQL is a powerful database.

MySQL is open-source software, but it's not technically freeware in all cases. There are a small, but clear set of conditions where you need to buy a $200/server license for MySQL. Basically you need to buy a license if: you make money from selling MySQL or charging to install or maintain it.

TcX also currently requires that you buy a license if you use the Windows version to help defray the additional costs of porting to the Windows platforms. You are also obligated (though not strictly required) to buy one of the support packages if MySQL is part of your mission-critical operations.

Since these costs are orders of magnitude less than commercial counterparts, there's no excuse for not contributing to the development effort through licensing and support packages.

Using MySQL with the Web

MySQL is an excellent general-purpose database, and includes native APIs that can be used to develop applications that incorporate native MySQL functionality. Web applications can certainly be written using one of these APIs, but can also be built using tools and APIs developed by others in the open-source community. While there are a number of combinations of tools, there are four major types of MySQL web database development:

  • PHP is easily the most common tool used with MySQL. Developers on both sides have provided a native API and the fact that both are open source and virtually free (and compatibly with Apache web server) makes this a natural choice. See Jason Gilmore's article for more information.
  • CGI provides a tried and true way for programmers to build web functionality. APIs exist for C, C++, Eiffel, Java, Perl, Python, and Tcl so hackers can use their favorite tool to build traditional CGI web applications.
  • ODBC with middleware such as Allaire's ColdFusion or Microsoft ActiveServer Pages is an option when combined with the freely available MyODBC driver. A number of developers deploying ColdFusion on Solaris and Linux chose MySQL to reduce overall costs while maintaining a high level of availability and robustness.
  • Java using JDBC, typically in either a servlet or JSP environment. Several JDBC drivers, including a commercial one, are available to enable this solution.

The bottom line is that there is a way to use MySQL with virtually any major web application platform. While I can't recommend PHP enough as a companion to MySQL, I've also personally had good luck with ColdFusion and Perl.

Choosing YourSQL

MySQL is a solid, low-cost, high-performance platform for both building and deploying web database applications. It scales to fairly large and busy sites with ease and is suitable for a number of web application development tools.

It's not the right platform for a transaction-intensive financial system, nor is it the right system if you deal with terabytes of data. It may also not be the right system if you've got a few Oracle DBAs already on staff. But for projects ranging from personal hacking to dynamic news sites and search engines, it can provide all the power you need at the right price.

John Paul Ashenfelter is president and CTO of TransitionPoint.com, a technology development and analysis group focusing on web database applications.


Discuss this article in the O'Reilly Network Forum.

Return to the O'Reilly Network Hub.