Hi, Sergey! Here's my opinion below: On Mar 20, Sergey Petrunia wrote:
Hello Igor and others,
== dropping table statistics == How does one drop table statistics? are we satisfied with needing to run three statements
DELETE FROM mysql.column_stats WHERE schema_name=$db AND table_name=$table_name; DELETE FROM mysql.index_stats WHERE table_name=$db AND table_name=$table_name; DELETE FROM mysql.column_stats WHERE table_name=$db AND table_name=$table_name;
or we want a simpler SQL, like
DROP STATISTICS FOR t1;
DELETE is fine. See also the answer below
== Permissions on statistic tables ==
What should be the permissions on statistics tables? I guess we should follow pattern from other system tables - a user is typically able to read the table but not write to it (so that he can't mess with other users' SPs/triggers/etc).
However, if the user is not allowed to modify the table directly, we should provide enough ways to modify the table indirectly. For example, we should provide a way to drop statistics for one's tables (which brings us back to the previous item). We might also want to allow to make other modifications to statistics of tables that one has control of?
No need to. Statistics is mostly internal, one shouldn't need to drop or modify it besides ANALYZE TABLE. DBA can, of course, mess with it any way she wants, but that's all - otherwise the statistics is pretty much automatic and invisible. I don't think it needs special maintenance statements or special privilege checks or anything. Of course, a DBA can create a set of SPs and views to see and modify statistic tables. But the database server itself doesn't need to support anything special for it. Regards, Sergei