Why Can't Database Tables Index Themselves?
Obviously, indexes are a central concept to databases and database performance. But horror tales still abound of naive developers who "forget" to index their tables, and encounter massive performance and scalability problems down the road as their tables grow. I've run into it personally, and I've read plenty of other sad tales of woe from other developers who have, too. I've also forgotten to build indexes myself on non primary key columns many times. Why aren't databases smart enough to automatically protect themselves from this?
It always struck me as absurd that I had to go in and manually mark fields in a table to be indexed. Perhaps in the bad old file-based days of FoxPro, DBase, and Access, that might have been a necessary evil. But in a modern client-server database, the server should be aware of all the queries flowing through the system, and how much each of those queries cost. Who better to decide what needs to be indexed than the database itself?
Does this query result in a table scan? If so, determine which field(s) could be indexed, for that particular query, to remove the need for a table scan. Store the potential index in a list. If the potential index already exists in the list, bump its priority. After (some configurable threshold), build the most commonly needed potential index on the target table.
