On Thu, May 15, 2014 at 4:14 AM, Johan De Meersman <vegivamp@stripped> wrote:
> You've already had some good advice, but there's something much more simpler that
> will also give you a significant boost: a covering index.
> Simply put, the engine is smart enough to not bother with row lookups if everything
> you asked for is already in the index it was using. You'll need to keep the index
> requirements in mind, of course (most selective fields first, order fields after selection
> fields, etc) and then append any other fields to the same index.
> Hard to say more without actual use cases, of course, but it's well worth looking
> into as it requires no other changes in application or schema.
This table is queried based on requests from the users. There are 10
different lookup columns they can specify, and they can provide any or
all of these. Currently each one of the columns has an index on it.
Would it be beneficial to create an index with all 10? Rarely are all
10 specified in the query. Typically it's 3 or 4. Would it be
worthwhile to see which are much commonly specified and create an
index with just those? Or would it be better to put the commonly
selected columns on the index with the lookup columns?