May 28, 2012 at 1:45 pm
GilaMonster (5/28/2012)
Please, please don't trust those index suggestions. They're made on the basis of a single query and if you follow them blindly without testing or considering the rest of the workload you can get so many near duplicate indexes that it's not funny.
Oh no, I do know better than to take such things on faith. But it certainly helped here. As it suggested different indexes, I tried them, compared them with others already in place and removed old ones, with tests after each change. I thought I already had a good index in place, based on what I was trying to join in another table. But the optimizer basically told me to go roll my hoop, it wasn't interested. When I put in what it suggested, the response time went from half a minute to instantaneous. I still have three near-identical indexes in place, all from various suggestions, but I'm going to work on those now, see which ones have any impact on performance. I'm pretty sure that I can get it down to one, maybe with one or two additional included columns. Fortunately, this table is only used in a few places, so I can quickly test them all.
Appreciate the warning, though. You're certainly right about the potential proliferation of redundant indexes - I saw that immediately, as I started trying the various suggestions. "Put not your faith in automated software." (But don't be afraid to examine the ideas it offers :cool:) Dealing with the optimizer seems almost like a fishing trip - dangle an index here, a restriction there, and see what it goes after.
Viewing post 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply