Specifications

Speeding Up Queries with Indexes

If you are in the situation mentioned previously, in which the possible_keys column from an

EXPLAIN contains some NULL values, you might be able to improve the performance of your

query by adding an index to the table in question. If the column you are using in your WHERE

clause is suitable for indexing, you can create a new index for it using ALTER TABLE like this:

ALTER TABLE table ADD INDEX (column);

General Optimization Tips

In addition to the previous query optimization tips, there are quite a few things you can do to

generally increase the performance of your MySQL database.

Design Optimization

Basically you want everything in your database to be as small as possible. You can achieve this

in part with a decent design that minimizes redundancy. You can also achieve it by using the

smallest possible data type for columns. You should also minimize NULLs wherever possible,

and make your primary key as short as possible.

Avoid variable length columns if at all possible (like VARCHAR, TEXT, and BLOB). If your tables

have fixed-length fields they will be faster to use but might take up a little more space.

Permissions

In addition to using the suggestions mentioned in the previous section on EXPLAIN, you can

improve the speed of queries by simplifying your permissions. We discussed earlier the way

that queries are checked with the permission system before being executed. The simpler this

process is, the faster your query will run.

Table Optimization

If a table has been in use for a period of time, data can become fragmented as updates and

deletions are processed. This will increase the time taken to find things in this table. You can

fix this by using the statement

OPTIMIZE TABLE tablename;

or by typing

>myisamchk -r table

at the command prompt.

Advanced MySQL

HAPTER 11

ADVANCED

SQL

261

14 7842 CH11 3/6/01 3:35 PM Page 261