The way you use your SQL statements can have a large effect on your Web applications' performance. For example, retrieving a long list of records for display on a single continuous Web page is a mistake. You should retrieve only a portion of the records (10 or 50, for example) at a time and then have a "Next 50" button or link on your Web page to retrieve the next set of records.

When you write such code, make sure to take advantage of SQL. The limit keyword, for example, limits the number of returned records. The offset keyword skips an offset of records and then returns the next ones. To return the third group of 50 customer records, you would use a query similar to the following:

SELECT customer_id, customer_name from customer ORDER BY customer_id LIMIT 50 OFFSET 100;

If your tables hold many columns or you're using joins in your queries, you should not use select * syntax just to save you from typing the fields. Making the effort to type the fields you need saves a few CPU cycles every time you run the code.

While we're on the subject of select statements, make sure you use where clauses to your advantage. If you have multiple columns in the where clause, the performance will vary depending on the order of the columns. The first column in the clause should result in the smallest record set returned, the second column in the second smallest and so on for the rest of the columns.

>> Use Case Statements

When pondering a query that requires a decision based on the result, you have two approaches. The more obvious, and slower, approach is to execute the query and then test the value of the result in your application code. The more subtle and better approach is to use some of the advanced features of SQL. The case statement in SQL, much like its counterpart in most development languages, can pick one of several choices depending on the input parameter. In this instance, the result of a select can be controlled by using a case statement, as in the following:

select product_name,
CASE
when price < 5 then 'cheap'
when price > 5 and price < 20 then 'OK'
else 'too expensive for my taste'

END as product_price
from Products order by product_name;

The result will be a two-column record set, with the first column being the product name and the second being our chosen interpretation of the price.

MySQL

Because of its nature and development history, MySQL has some specific performance issues. For example, MySQL performs best on Intel machines running Linux. There are many reasons for this, but the primary reason is the way memory allocation works. Therefore, if you are using MySQL, do not use Microsoft Windows NT and vice versa.

In general, MySQL is known to be very fast. Some claim it is much faster than any other database engine available. TcX (the company behind MySQL) has set up a Web site (www.tcx.com) that compares and contrasts MySQL with other databases and displays performance comparisons on multiple platforms. According to the published results, MySQL outperforms all other database engines by an average of 40 percent. Of course it's best to maintain a healthy scientific skepticism.

This need for speed in MySQL comes at an expense. MySQL does not support transactions. Transactions slow down the performance of a database engine. They also require log files so they can be undone or rolled back. Having a log file requires a system administrator to babysit the file and make sure it does not grow too large. Log files also need to be backed up with your database.

>> Constraints

A constraint is a method of enforcing relationships between tables and thereby ensuring data integrity. The main advantage of using a constraint is to guard against programming mistakes. MySQL does not use constraints as a general practice -- and neither should you. It is much better to have your application logic enforce the functional requirements and ensure data consistency than to have your application crash on a user at 3 a.m. because a constraint that was not accounted for in your code was violated. Some programmers spend more time working around constraints and debugging problems caused by violating constraints than they do coding the proper logic in their programs. In addition, you have a better chance of developing a portable application if you do not use them.

>> Table Types in MySQL

MySQL has four table types: static, dynamic, heap and compressed. A static table type is the fastest of the three on-disk table types, according to the MySQL manual. However, static tables cannot contain variable-length columns. Having even a single variable-length column forces MySQL to create a dynamic table instead of a static table. Dynamic tables hold much more information, particularly about the size of the table, and are much slower than static tables.

The two other table types are for special tables. The heap exists only in memory, and thus it is extremely fast, but it should be used only with small- and medium-sized tables. The compressed table is a read-only table, and it also is very fast. The MySQL manual, at www.mysql.com/doc, provides more detailed information on the different table types.

>> Dead Space in MySQL

When variable-length columns are updated with shorter data, dead space results in the MySQL data files. There is no programming solution to this problem. Another performance problem arises with normal usage of indexes, because they degrade over time. MySQL has a tool called myisamchk that can be used to free up dead space and to reoptimize indexes. This tool should be run periodically against your database to keep things in check.

PostgreSQL

Unlike MySQL, PostgreSQL supports stored procedures, constraints and transactions. There are no shortcuts taken in implementing functionality for the sake of performance. But PostgreSQL's rich feature set has its advantages. PostgreSQL has two sophisticated built-in features that can be used to improve performance -- the commands vacuum and explain.

When PostgreSQL updates a row, it keeps the original row and creates a new row at the end of its internal data files. The old row is marked as expired and is used by other transactions still looking at the older state of the database before the current transaction is committed. The same process applies to deleted rows. The vacuum command deletes expired rows from the database and compacts the database. This command should be run periodically to clean up the database.

An important method of optimizing your code is to optimize the way your query performs. Merely eyeballing a query to identify the bottlenecks will get you nowhere fast. Instead, most databases have an analyze functionality that analyzes the query for you instead of executing it. In PostgreSQL the functionality is called explain. An example of its usage is:

EXPLAIN SELECT phone_
number from people where id=930;
NOTICE: QUERY PLAN:
Seq Scan on people (cost=0.00..30.50 rows=20 width=15)

The database is reporting that a sequential scan is required. The cost numbers are meaningful only by comparison. The rows value reports the expected number of rows to be returned by the query. The final value--width--reports the number of bytes per row. If you perform a vacuum command against this database, you probably will notice an improvement in the performance reported by explain. Furthermore, creating an index on the phone_number column will reduce the query to an index scan instead of a sequential scan.