Analyze Table

MySQL & MariaDB Other MySQL® Questions
1

Hello,

I’m having a tough time understanding what ANALYZE TABLE does.
Mysql.com says “ANALYZE TABLE analyzes and stores the key distribution for a table”

Key distribution being index or indexes? Why does it store it, isn’t it already stored when index is created, or when row is inserted (if index already exists). Isn’t it already stored and waiting to be invoked by the Opimizer?

2

Index statistics are there to help the optimizer choose a good plan for your query. It needs to know something about the distribution of data in order to choose the use of one index over another or to realize that no index is selective enough and that it should instead perform a table scan. Sure, the index itself will tell you the distribution, but if you’ve indexed 50 million rows, reading that every time MySQL needs to make a decision about using an index would make your query even slower. Instead, we can arrive at a good estimate of this distribution by simply sampling the data. We can only a small percentage of the rows and come up with information that is good enough to make decisions about how to execute a query. All the ANALYZE statement does is ask MySQL to re-sample that data.

3
[B]vgatto wrote on Fri, 26 December 2008 09:04[/B]
Index statistics are there to help the optimizer choose a good plan for your query. It needs to know something about the distribution of data in order to choose the use of one index over another or to realize that no index is selective enough and that it should instead perform a table scan. Sure, the index itself will tell you the distribution, but if you've indexed 50 million rows, reading that every time MySQL needs to make a decision about using an index would make your query even slower. Instead, we can arrive at a good estimate of this distribution by simply sampling the data. We can only a small percentage of the rows and come up with information that is good enough to make decisions about how to execute a query. All the ANALYZE statement does is ask MySQL to re-sample that data.
I see, Thank You for explaining.

The other function that I always see mentioned along with ANALYZE is OPTIMIZE. From what I’ve read ‘OPTIMIZE’ is more of a defrag feature, but there is an article that I’ve read that mentions that it “refreshes” indexes, is that right?