The beauty of ORM lulled me into a soporific sleep.
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?
views:
380answers:
2
+4
A:
Yes, there is.
If you take a look at the slow query log, there's an option --log-queries-not-using-indexes
Greg
2009-01-13 10:40:25
Too bad there's no post about more generic solution, like the middleware mentioned.
ohnoes
2009-01-13 10:50:44
The above mentioned middleware will show only columns that are candidates for indexes, while slow query log will show you actual suggestions (which might be different due to server optimizations specific to MySQL).
zgoda
2009-01-13 11:44:28
+4
A:
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.
S.Lott
2009-01-13 11:32:53
In my case as in many web apps it's read-heavy - writes are admin-only and table size is fairly small. Would you agree in this case that adding indexes to all columns used in lookups would be beneficial?
andybak
2009-01-13 13:54:50
@andybak: Willy-Nilly indexing is a bad plan. Some indexes can seem like a good idea but have to real benefit. Two rules: (1) Change One Thing At A Time; (2) Measure Before and After. Never shot-gun a bunch of changes and hope.
S.Lott
2009-01-13 13:57:35