MySQL: Choosing the most important features

When choosing a database, an expert recommends making sure it can support the volume of data you expect and retrieve it quickly in a readable format.

When choosing a database, MySQL expert Scott Noyes recommends that you make sure it can support the volume of data you expect and retrieve it quickly in a readable format. Bookit.com's senior Web applications developer also gives a list of major companies, like Google, who use MySQL.

Enterprise customers want scalability, reliability, redundancy and security, but some database programmers are looking for features such as solid transaction support, stored procedures and more functions. How do IT managers decide what features are most important?

Scott Noyes: Two terms come into play when choosing a data storage engine for business. OLAP (Online Analytical Processing) generally concerns analysis of data to support business decisions -- comparing yearly profits, building cross tabs or aggregating customer demographics. OLTP (Online Transaction Processing) generally concerns the daily process of business -- shopping carts, inventory and the general ledger.

More on MySQL:
MySQL's Marten Mickos on GPLv3 and automated enterprise monitoring

Mastering MySQL: Drivers, clustering and remote access

You want to pick a database which can support the volume of data you expect, retrieve it in a timely fashion and present it in a usable form. Choose the database that is best able to handle those three needs.

How do IT managers fight the perception of management that MySQL is not suitable for 'the real world' because it is free?

Noyes: Big companies like Google use MySQL successfully. If management is afraid that they'll be left hanging when something breaks, suggest a subscription to MySQL Network, or even consider the new MySQL Enterprise package. Enterprise offers the same software your developers already know, but adds the support and services a manager needs to be confident in a system.

When using MySQL administrator to manage MySQL, how can users access other applications without being restricted to using "root"?

Noyes: Try granting that user some privileges.

If that doesn't solve the problem, check the user's host. Using Query Browser or the command line client, while connected as root, issue the following command:

SELECT user, host FROM mysql.user WHERE user = 'theUserYouCreated';

Check the `host` field. If it is set to the wildcard "%," your user should be able to connect from anywhere. If it is set to "localhost," your user can only connect from the server on which MySQL is running. Use the GRANT statement to give user access from "%."

For users interested in learning how to work with and create databases, can you offer them any advice?

Noyes: A wide variety of database brands exist, and an even wider variety of languages to use with those databases. The combination to pick depends on your budget and your goals.

The Microsoft products (Access and Visual Basic) are ideal for many small projects. They integrate with each other easily, and rank with FileMaker for friendliest user interfaces available, which make them easy to learn. Both companies offer training and cost breaks for schools. However, they are not free, have limited portability (it's hard to run Access on a Linux server) and some design limitations render them of limited use for larger projects.

If you're on a tighter budget, or need something a little more robust or portable, MySQL and PostgreSQL are very powerful database systems. They both run on a variety of platforms, and can be used with either a standalone client or web-based architecture. SQLite supports many of the standard features of bigger databases, but has a very small footprint and almost no need for configuration. The licenses for each vary, but generally you can use them at no charge until you are ready to start distributing your application to others.

One comforting thought is that it is possible (perhaps not trivial, but possible) to transfer all your data from any database to any other database. So feel free to just pick one and try it -- if you don't like it, or you discover another one better suits your needs, you can always switch.

Some other languages to consider include scripting languages like PHP, Python or Ruby, which are popular for running your application via the web. You can also write standalone applications in any of those languages, but it is more common to write such programs in Java or C/C++.

Check your local public library, community college and adult education center for classes -- many offer introductions to databases, often geared towards genealogical research.

Your library may have books on programming, but the computing world moves so fast that most libraries fall quickly out of date. Amazon.com has most of the current books. The "For Dummies" books are popular, interspersing the technical jargon with quips and cartoons. I recommend Peachpit's "Visual QuickStart Guide" series for something a little less chatty.

There are thousands of tutorials on the web which can walk you through everything from installing a database to compiling your code. Searching for "access tutorial" or "PHP tutorial" results in plenty of reading material.

Every one of those databases and languages has a community scattered across the Internet in forums and chat rooms, who are happy to answer specific questions whenever you get stuck. Here's a few of them:

  • http://www.experts-exchange.com
  • http://www.qunu.com
  • http://www.devshed.com

This was first published in December 2006

Dig deeper on Linux administration tools

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataCenter

SearchServerVirtualization

SearchCloudComputing

SearchEnterpriseDesktop

Close