Databases Reference
In-Depth Information
strategies that work for queries like this one. An even better strategy is to limit the
number of pages you let the user view. This is unlikely to impact the user's experience,
because no one really cares about the 10,000th page of search results.
Another good strategy for optimizing such queries is to use a deferred join, which again
is our term for using a covering index to retrieve just the primary key columns of the
rows you'll eventually retrieve. You can then join this back to the table to retrieve all
desired columns. This helps minimize the amount of work MySQL must do gathering
data that it will only throw away. Here's an example that requires an index on
(sex,
rating)
to work efficiently:
mysql>
SELECT
<cols>
FROM profiles INNER JOIN (
->
SELECT
<primary key cols>
FROM profiles
->
WHERE x.sex='M' ORDER BY rating LIMIT 100000, 10
->
) AS x USING(
<primary key cols>
);
Index and Table Maintenance
Once you've created tables with proper data types and added indexes, your work isn't
over: you still need to maintain your tables and indexes to make sure they perform well.
The three main goals of table maintenance are finding and fixing corruption, main-
taining accurate index statistics, and reducing fragmentation.
Finding and Repairing Table Corruption
The worst thing that can happen to a table is corruption. With the MyISAM storage
engine, this often happens due to crashes. However, all storage engines can experience
index corruption due to hardware problems or internal bugs in MySQL or the operating
system.
Corrupted indexes can cause queries to return incorrect results, raise duplicate-key
errors when there is no duplicated value, or even cause lockups and crashes. If you
experience odd behavior—such as an error that you think shouldn't be happening—
run
CHECK TABLE
to see if the table is corrupt. (Note that some storage engines don't
support this command, and others support multiple options to specify how thoroughly
they check the table.)
CHECK TABLE
usually catches most table and index errors.
You can fix corrupt tables with the
REPAIR TABLE
command, but again, not all storage
engines support this. In these cases you can do a “no-op”
ALTER
, such as altering a table
to use the same storage engine it currently uses. Here's an example for an InnoDB table:
mysql>
ALTER TABLE innodb_tbl ENGINE=INNODB;
Alternatively, you can either use an offline engine-specific repair utility, such as
myi-
samchk
, or dump the data and reload it. However, if the corruption is in the system
area, or in the table's “row data” area instead of the index, you might be unable to use
any of these options. In this case, you might need to restore the table from your backups
or attempt to recover data from the corrupted files.
