Not an expert, but: 2009/11/14 Krishna Chandra Prajapati <prajapat...@gmail.com>: > I would like to know how optimize table work internally. A table contains > 200 millions records. Whether query performance will be faster after > deletion of 30 million records from this table or not.
Use of OPTIMIZE TABLE varies from engine to engine (MyISAM vs. InnoDB vs. ARCHIVE) but, generally, it will perform the following actions: * Regain data space, defragmenting deleted rows * Repairing indexes * Updating statistics that enables better optimizer execution plans (similar to ANALYZE TABLE) Deleting > 10% of rows is the paradigmatic case where optimize table should be run to improve performance. However, these optimizations are engine-dependant, as for example, InnoDB without the 'multiples tablespaces' option will not release free space, for performance reasons. An important drawback that you must consider is that executing OPTIMIZE TABLE is very process-heavy and that it will block your entire table even for read (unless PARTITONS are used). It could take some time if we are talking about millions of records. If you need High Availability, you will have to use replication or any other way of load balancing while performing administrative tasks. If you could have a pre-production host to test these kind of operations it would be great! Each application is a world on its own. You can read more info here: <url:http://dev.mysql.com/doc/refman/5.1/en/optimize-table.html> -- Jaime Crespo MySQL & Java Instructor Warp Networks <http://warp.es> -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
