application check this table to see if a user has the necessary permissions to access
the database. If the user passes this test, use the user in the grant table for the rest of
the access to the database.
Counting all rows in a database using SELECTCOUNT(*), is very fast—it uses the
number contained in the table's header.These are just some general tips to make a quicker query. The best gains are from the statements
themselves.Building a Better WHERE Clause
The best way to start building your WHERE clause is to look at the available indexes you have for the tables
you are going to query. If a table has an index, use it; this will speed things up considerably. If you can not
rewrite the WHERE clause, create an index.
Remember that conditional statements execute faster with numerical values than they do with string or
character values. Compare numbers whenever possible.
The LIKE statement can slow things down as well. You may use an index with a LIKE comparison if
the constant that is being compared does not have a wildcard character in the first position. For
example, the following statement will use the index if the Last_Name column has one:
SELECT State FROM Customers WHERE Last_Name LIKE "Rober%"However, an index will not be used for the following statement:
SELECT State FROM Customers WHERE Last_Name LIKE "%obert%"Getting Rid of Dead Space
When a record is deleted from a file, or a column that contains variable length fields changes, a dead spot is
created in the file that stores this data. See Figure 20.1.
Figure 20.1 A variable-length column before and after an UPDATE.
This dead space can accumulate, taking up disk space and slowing things down. There is no way to
avoid this completely. (Not using variable length fields helps).
Another problem that can affect database performance is the key order. Suppose you have an index on
a unique AUTO_INCREMENT column. You have done many inserts and a few deletes, and the numbers
may have fallen out of order. This can affect your index's performance—think what it would be like to
find someone's file if the files were no longer in alphabetical order.
Fortunately, the developers at MySQL saw these potential problems and have provided a tool to fix
them. This little tool is called myisamchk. It resides in the bin directory of a normal mysql installation
directory.
The myisamchk tool can be used to help optimize tables and keys, much like the OPTIMIZE command,
only quicker. This utility can also help repair damaged tables as well. The command is as follows:
myisamchk options table_name
Where table_name is the name of a table or a group of tables designated by a wildcard. To perform
this check on all your tables in the data directory, you could issue the following command:
bin/myisamchk /usr/local/mysql/data/*/*.myi
This will check all data files in the data directory. The myisamchk utility can take the following options:
-a or -analyze Analyzes the paths from tables to see how it can make joins faster.
-d or -description Outputs some information about the table, such as the record
length, record format, number of records, deleted blocks, and key information.
-f or -force Automatically overwrites any temporary files.