As others said, this is dangerous to automate. But you can simplify your life if you rely on the 80/20 rule. (80% of the benefit can be had for 20% of the work.)

The principle is simple. The practice is harder. The principle is that you want to turn on profiling to be able to identify your top performance hogs. One by one try to optimize them. After a few hotspots are improved, your database should be happier. Repeat every time your load is becoming a problem again.

I don't know too much about what profiling tools exist for postgres and mysql, however in a quick google I turned up this article that describes one which may do the trick. You probably want to look for more on this. (In particular I know that Oracle has a ton of tools for monitoring different kinds of stresses that queries put on the database. For instance you can identify what queries take the most disk accesses, internal locking, CPU time, etc...)

In reply to Re: Autogenerate SQL Indexes? by tilly
in thread Autogenerate SQL Indexes? by knoebi

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.