The ANALYZE TABLE entry in the manual doesn't say anything about performance optimization.
How do I get optimum performance out of ANALYZE TABLE?
Can I just set the same variables as I would with REPAIR TABLE?
key_buffer_size sort_buffer_size myisam_sort_buffer_size
Also... Does A.T. always need to read ALL rows from the table? I assume so.
Also... why does it require *any* type of lock? For large tables the chance that data would be modified during the ANALYZE to cause the results to be wildly innacurate are rare. It seems possible to just let ANALYZE run without any locks and just let the data be off by a few K rows. Which seems MUCH better than NEVER running them.
Kevin:
ANALYZE table does not do any sorting, so the setting of myisam_sort_buffer_size should not affect its performance. However, since it has to traverse the B-trees of the keys, a large key_buffer_size should be helpful (although it depends on how well the OS caches the data).
The lock is needed not only to ensure the consistency of the results, but also to keep the server from crashing. If some other thread is in the middle of modifying the key structure, some pointers in the data structures could be invalid, and cause the ANALYZE thread to crash (and take the whole server down with it).
It should be possible to modify the server to ANALYZE a key at a time, or even a key range at a time, but I do not anticipate it happening in the near future. If the optimizer is making bad choices because of incorrect key statistics, you can instruct it to use the correct key with FORCE INDEX
-- Sasha Pachev Create online surveys at http://www.surveyz.com/
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
