On 20 Feb 2016, at 8:48pm, Dave Baggett <dmb at inky.com> wrote: > I have covering indices that make some of our common queries run a lot > faster. It turns out that the vast majority of my writing to disk is simply > to maintain these indices. Obviously, I will remove as many indices as I can, > but as it is right now I'm seeing sustained write rates of 4MB/sec -- almost > entirely to keep indices up to date -- which seems insane.
Create indexes only for each of your WHERE/GROUP BY/ORDER BY clauses. If you have a long index, and shorter index which is the start of it, you don't need the shorter index. If you have long periods of SELECTing, and short periods where you make lots of changes, it can be more efficient to DROP the indexes while you're making your changes. Lastly, don't waste time in premature optimization. Write your code as the languages suggests you should. Then try it. Only if the result of that is too slow should you worry about optimization. Simon.
