Oracle 5.0 Reference Manual Download

Page: 711 / 3232

Controlling the Query Optimizer

691

as information about the last displayed banner for users who don't have cookies enabled in their Web
browser. User sessions are another alternative available in many Web application environments for
handling volatile state data.

• With Web servers, images and other binary assets should normally be stored as files. That is, store

only a reference to the file rather than the file itself in the database. Most Web servers are better at
caching files than database contents, so using files is generally faster.

• Columns with identical information in different tables should be declared to have identical data types

so that joins based on the corresponding columns will be faster.

• Try to keep column names simple. For example, in a table named

customer

, use a column name of

name

instead of

customer_name

. To make your names portable to other SQL servers, you should

keep them shorter than 18 characters.

• If you need really high speed, you should take a look at the low-level interfaces for data storage that

the different SQL servers support. For example, by accessing the MySQL

MyISAM

storage engine

directly, you could get a speed increase of two to five times compared to using the SQL interface. To
be able to do this, the data must be on the same server as the application, and usually it should only
be accessed by one process (because external file locking is really slow). One could eliminate these
problems by introducing low-level

MyISAM

commands in the MySQL server (this could be one easy

way to get more performance if needed). By carefully designing the database interface, it should be
quite easy to support this type of optimization.

• If you are using numeric data, it is faster in many cases to access information from a database (using

a live connection) than to access a text file. Information in the database is likely to be stored in a
more compact format than in the text file, so accessing it involves fewer disk accesses. You also
save code in your application because you need not parse your text files to find line and column
boundaries.

• Replication can provide a performance benefit for some operations. You can distribute client

retrievals among replication servers to split up the load. To avoid slowing down the master while
making backups, you can make backups using a slave server. See

Chapter 16, Replication

• Declaring a

MyISAM

table with the

DELAY_KEY_WRITE=1

table option makes index updates faster

because they are not flushed to disk until the table is closed. The downside is that if something kills
the server while such a table is open, you should ensure that the table is okay by running the server
with the

--myisam-recover

[415]

option, or by running

myisamchk

before restarting the server.

(However, even in this case, you should not lose anything by using

DELAY_KEY_WRITE

, because

the key information can always be generated from the data rows.)

8.4. Controlling the Query Optimizer

The task of the query optimizer is to find an optimal plan for executing an SQL query. Because the
difference in performance between “good” and “bad” plans can be orders of magnitude (that is,
seconds versus hours or even days), most query optimizers, including that of MySQL, perform a more
or less exhaustive search for an optimal plan among all possible query evaluation plans. For join
queries, the number of possible plans investigated by the MySQL optimizer grows exponentially with
the number of tables referenced in a query. For small numbers of tables (typically less than 7 to 10)
this is not a problem. However, when larger queries are submitted, the time spent in query optimization
may easily become the major bottleneck in the server's performance.

MySQL 5.0.1 introduces a more flexible method for query optimization that enables the user to control
how exhaustive the optimizer is in its search for an optimal query evaluation plan. The general idea is
that the fewer plans that are investigated by the optimizer, the less time it spends in compiling a query.
On the other hand, because the optimizer skips some plans, it may miss finding an optimal plan.

Oracle 5.0, Reference Manual

Search results

Summary of Contents for 5.0

Reviews:

Related manuals for 5.0

Brands by name

Popular brands