Hudzilla.org - the homepage of Paul Hudson

18.2.2 Optimise your tables

This is NOT the latest copy of this book; click here for the latest version.

The SQL statement "OPTIMIZE TABLE table " lets you specify a table (or several tables, with commas in between) that you would like MySQL to optimise. Notice the spelling is with a Z in the statement. By "optimise", I mean tables that have had a lot of changes made to them since they were created - lots of rows added/deleted/edited, etc; especially if the table uses varying length fields like VARCHARs and TEXTs. OPTIMIZE TABLE instructs MySQL to basically defragment the table to make it faster to read and write from.

<< 18.2.1 Prioritise your data		18.2.3 Select as little data as possible >>
Table of Contents

Want to see this stuff in print? PHP in a Nutshell takes the core topics covered here, adds in thousands of edits from the editorial team and myself, and combines them to make an unbeatable reference for PHP programmers at all levels.

My latest book has hundreds more tips on how to use PHP, Apache, and MySQL, plus Perl, Python, shell scripts, performance tuning, and more!

Andrey - 05 Dec 2008

MySQL also updates its statistics which are used internally by the optimizer during query optimization. Especially the cardinality of the values in columns. This is important since if a value has low-cardinality, let's say more than 30% of the rows in a table have this value then the optimizer will do a table scan - always instead of index scan, since table scan is less expensive than index scan when more than 30% of the rows are read.

Add comment
Please note that by posting a comment here you are committing it to the public domain. This is important so that others can make use of your code themselves, and also so that I can incorporate helpful notes directly into the main text. Comments are limited to 2000 characters in length.

If you are reporting an error in the content, please tell me directly.