Performing Database Maintenance


MySQL features a series of statements that can (and should) be used to ensure that databases are correct and functioning properly.

Here are some statements you should be aware of:

  • ANALYZE TABLE is used to check that table keys are correct. ANALYZE TABLE returns status information, as seen here:

Input

ANALYZE TABLE orders;

Output

+--------------------+---------+----------+----------+ | Table              | Op      | Msg_type | Msg_text | +--------------------+---------+----------+----------+ | crashcourse.orders | analyze | status   | OK       | +--------------------+---------+----------+----------+

  • CHECK TABLE is used to check tables for a variety of problems. Indexes are also checked on a MyISAM table. CHECK TABLE supports a series of modes for use with MyISAM tables. CHANGED checks tables that have changed since the last check, EXTENDED performs the most thorough check, FAST only checks tables that were not closed properly, MEDIUM checks all deleted links and performs key verification, and QUICK performs a quick scan only. As seen here, CHECK TABLE found and repaired a problem:

Input

CHECK TABLE orders, orderitems;

Output

+------------------------+-------+----------+-----------------------+ | Table                  | Op    | Msg_type | Msg_text              | +------------------------+-------+----------+-----------------------+ | crashcourse.orders     | check | status   | OK                    | | crashcourse.orderitems | check | warning  | Table is marked as    | |                        |       |          | crashed               | | crashcourse.orderitems | check | status   | OK                    | +------------------------+-------+----------+-----------------------+

  • If MyISAM table access produces incorrect and inconsistent results, you might need to repair the table using REPAIR TABLE. This statement should not be used frequently, and if regular use is required, there is likely a far bigger problem that needs addressing.

  • If you delete large amounts of data from a table, OPTIMIZE TABLE should be used to reclaim previously used space, thus optimizing the performance of the table.




MySQL Crash Course
MySQL Crash Course
ISBN: 0672327120
EAN: 2147483647
Year: 2004
Pages: 214
Authors: Ben Forta

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net