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.
The behavior of the optimizer with respect to the number of plans it evaluates can be controlled using
two system variables:
Содержание 5.0
Страница 1: ...MySQL 5 0 Reference Manual ...
Страница 18: ...xviii ...
Страница 60: ...40 ...
Страница 396: ...376 ...
Страница 578: ...558 ...
Страница 636: ...616 ...
Страница 844: ...824 ...
Страница 1234: ...1214 ...
Страница 1426: ...MySQL Proxy Scripting 1406 The following diagram shows an overview of the classes exposed by MySQL Proxy ...
Страница 1427: ...MySQL Proxy Scripting 1407 ...
Страница 1734: ...1714 ...
Страница 1752: ...1732 ...
Страница 1783: ...Configuring Connector ODBC 1763 ...
Страница 1793: ...Connector ODBC Examples 1773 ...
Страница 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Страница 1842: ...Connector Net Installation 1822 5 Once the installation has been completed click Finish to exit the installer ...
Страница 1864: ...Connector Net Visual Studio Integration 1844 Figure 20 24 Debug Stepping Figure 20 25 Function Stepping 1 of 2 ...
Страница 2850: ...2830 ...
Страница 2854: ...2834 ...
Страница 2928: ...2908 ...
Страница 3000: ...2980 ...
Страница 3122: ...3102 ...
Страница 3126: ...3106 ...
Страница 3174: ...3154 ...
Страница 3232: ...3212 ...