Ungod wrote:

> propose for discussion an addition to the engine to over time gather a
> history of the most used order by's and the most used where criteria
> to create either an "automatic indexing" feature or even just a "what
> are the best indexes i should use on this dataset".

In the past, people proposed this sort of enhancement. To keep
statistics when a query plan results in a natural table-scan that would
have benefitted from an index, if an index existed.

With this cumulative data, the engine would either (a) create indexes
for you silently, or (b) keep its hands off the metadata, and instead
just produce an advisory report (including a SQL script of CREATE INDEX
statements) when asked. The behavior could be configurable per server
or per database.

Likewise, the engine could monitor non-usage of existing indexes, and
periodically advise dropping or deactivating indexes that are never
used. I guess this would also require keeping counting the total number
of queries on the same table.

Also, advice might include deactivating indexes that are used regularly,
but have very poor selectivity. Such indexes might cost more to
maintain than is justified by the meager benefit they give.

This "advice" feature was at times given the cute name, "database
copilot". It makes me think of the cartoon character in Microsoft
Office who pops up offering help when I hit a wrong key.

Regards,
Bill Karwin