That being said, you could analyze your queries for what is being joined on in tables to build yourself a list of candidates, then address them each by hand. That would at least give you a starting point, I suppose.
Be warned, however, I am not sure if there are any kind of CPAN modules, but having written my own code to parse SQL (poorly) in the past, it is not for the faint of heart, especially if you a going cross-platform.
Write something and use it as a guide to narrow your options, but make the changes yourself, and test them. I have made slight changes to MySQL and actually slowed down a query because the optimizer got confused. EXPLAIN is your friend.
HINTS:
You may have to put something into DBI. If the queries don't specify what database the tables are in explicitly, and you have tables of the same name in different databases, then you will need a preliminary query to get your context.
Parse the from, get a list of tables and their aliases (if any), describe the tables into a hash for lookup if you have fields that don't have a table specified.
Parse through the where, check any comparitors that are simple field to field comparisons and ignore any others, this will get you most of your key situations and simplify your life.
Keep track of how many times a table is accessed and how many times each field in that table is accessed, if you have 20 accesses to a table, but a field is accessed only once in those 20, it is less likely to need keying.
Good Luck.
In reply to Re: Autogenerate SQL Indexes?
by suaveant
in thread Autogenerate SQL Indexes?
by knoebi
| For: | Use: | ||
| & | & | ||
| < | < | ||
| > | > | ||
| [ | [ | ||
| ] | ] |