Asking the db engine to suggest index creation/deletion

Is there any way for us to query the db to suggest index creation/index deletion that would improve the performance of开发者_StackOverflow中文版 the db system?

We understand that a dba can manually view the trace files to create/drop indices but can i write a java program that queries the db engine to suggest the same automatically.

Or some open source tools that i can check out to perform the same automatically.

Thx.

Well there's no standard JDBC way to do this. There may be specific driver implementations for specific DBS that would allow you to EXPLAIN your query (trace the use of indexes), etc. But there's no one-size fits all answer here.

in general I would lean to saying NO.

Your index performance is dependent on the queries you would fire on them...So No !

With MySQL specifically, you can flag slow queries, as well as queries not using indexes.

Ultimately, the database will do its best (within what you've created) to optimize your query.

http://dev.mysql.com/doc/refman/5.0/en/slow-query-log.html

This data would be logged to a file, and not necessarily available via an API.