The next biggest detriment is the hassle and frustration that constraints can cause. Working around
constraints, especially when deleting records, is a headache. This headache is multiplied tenfold when
the database is poorly designed.MySQL does not support constraints. Constraints only add to the overhead and slow things down, which
goes against the primary reason for using MySQL. The power is in the hands of the developer and the
DBA. It is their responsibility to ensure data integrity and that table relationships are enforced. If you
write good, database-aware applications and the schema is clear and easy to understand (good
design), the need for constraints, much like triggers or any other extra feature, is minimal.Summary
There are a lot of nice features that are available for databases. Stored procedures, triggers, and cursors
provide more options for DBAs and developers. These options come at an expense. The price you pay is
speed and complexity. MySQL can accomplish everything that can be done with these options, maybe not in
the same way, but you can get the same results because of the flexibility and speed that MySQL provides.
When choosing options for your database, think about the reason why those features are there. Stored
procedures are used because of the increased speed they provide compared to embedded SQL
statements. If you already have the speed, why would you need a stored procedure?Another thing to remember is that sometimes features tend to make people more lazy and sloppy.
Generally speaking, programmers as a whole are lazy people (or what they like to think of as being
efficiency minded)—always trying to make things easier. These features give a programmer an excuse
not to produce sloppy code and not spend a lot of time in the design phase of the database. This
doesn't mean that programmers who use constraints and stored procedures are sloppy and lazy—it just
means that they now have an excuse to throw in a constraint or create a trigger that does the job that
good design or good coding techniques could do.MySQL puts the power in the developer's hands. Come up with a good design and code your
application well, and you will not have any reason for stored procedures or any of the other features
discussed today, because MySQL provides you with the speed that caused the other databases to
invent those features.Q&A
Q: Why would I need to create a user-defined function?
A:
As explained today, UDFs can help fill the need for stored procedures. You
can accomplish predefined routines in a UDF. A UDF can also accomplish
very complex math and string manipulation functions—you have access to
all C++ libraries—before the results are returned to the user. UDFs are very
fast; They are compiled programs—not interpreted or parsed like SQL or
Perl.
Q:
If MySQL is missing so many of these features, why would I want to
use it?
A:
You are focusing on the negatives. MySQL may be missing some of the
features other databases have, but look at what MySQL has that other
databases lack:
MySQL can be used from the command line. This means
that you can control, access, and manipulate a MySQL
database remotely from anywhere—no need for third-party
tools, such as PC Anywhere or VNC.
MySQL is extremely fast—almost twice as fast as all its
competitors. Who needs extra features when your
database is as fast as MySQL?
MySQL comes with the source code. What other database
can say that?Exercises
- Go to the crashme Web site and compare MySQL to your favorite database.
- Create a user-defined function similar to the one in this chapter.