Optimizing Your Table Structure


An optimized table structure is different from a well-designed table. Table structure optimization has to do with reclaiming unused space after deletions and basically cleaning up the table after structural modifications have been made. The OPTIMIZE TABLE SQL command takes care of this, using the following syntax:

OPTIMIZE TABLE table_name[,table_name]


For example, if you want to optimize the grocery_inventory table in the testDB database, use

mysql> OPTIMIZE TABLE grocery_inventory; +--------------------------+----------+----------+----------+ | Table                    | Op       | Msg_type | Msg_text | +--------------------------+----------+----------+----------+ | testDB.grocery_inventory | optimize | status   | OK       | +--------------------------+----------+----------+----------+ 1 row in set (0.08 sec)


The output doesn't explicitly state what was fixed, but the text in the Msg_text column shows that the grocery_inventory table was indeed optimized. If you run the command again, you might see a message such as this one:

mysql> OPTIMIZE TABLE grocery_inventory; +-------------------------+----------+----------+-----------------------------+ | Table                   | Op       | Msg_type | Msg_text                    | +-------------------------+----------+----------+-----------------------------+ | testDB.grocery_inventory| optimize | status   | Table is already up to date | +-------------------------+----------+----------+-----------------------------+ 1 row in set (0.03 sec)


Be aware that tables are locked while undergoing optimization, so if your table is large, perform the optimization during scheduled downtime or when little traffic is flowing to your system.




Sams Teach Yourself PHP, MySQL And Apache All in One
Sams Teach Yourself PHP, MySQL and Apache All in One (3rd Edition)
ISBN: 0672328739
EAN: 2147483647
Year: 2004
Pages: 327

Similar book on Amazon

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