I would like to ask a question about the principle of index and optimization in database.
I am using mysql. The schema engine is myisam. In one query, the explain results showed 8000+ rows in a table that had been well indexed. Then my colleague used the command 'optimize table' in this table. And after that the explain showed 2 rows which looked correct. The result is good, but both of us do not really understand what really happened and why.
I am new in this area. So can anyone help to explain how this 'explain' and the index can be significantly changed after optimization? I thought index should be good enough before we optimize the table.
Many thanks!
You can read the manual on OPTIMIZE TABLE here: https://dev.mysql.com/doc/refman/5.7/en/optimize-table.html
For MyISAM tables, OPTIMIZE TABLE works as follows:
If the table has deleted or split rows, repair the table.
If the index pages are not sorted, sort them.
If the table's statistics are not up to date (and the repair could not be accomplished by sorting the index), update them.
It's the last step that is most useful in your case. This is the same work that is performed by ANALYZE TABLE. Read more about what that does here: https://dev.mysql.com/doc/refman/5.7/en/analyze-table.html
Both OPTIMIZE TABLE and ANALYZE TABLE do completely different things when using InnoDB. Read the docs to learn more.