Is there a way to automatically generate a list of columns that need indexing?

Question

The beauty of ORM lulled me into a soporific sleep. I've got an existing Django app with a lack of database indexes. Is there a way to automatically generate a list of columns that need indexing?

I was thinking maybe some middleware that logs which columns are involved in WHERE clauses? but is there anything built into MySQL that might help?

Answer 1

Yes, there is.

If you take a look at the slow query log, there's an option --log-queries-not-using-indexes

Answer 2

No.

Adding indexes willy-nilly to all "slow" queries will also slow down inserts, updates and deletes.

Indexes are a balancing act between fast queries and fast changes. There is no general or "right" answer. There's certainly nothing that can automate this.

You have to measure the improvement across your whole application as you add and change indexes.