Individual indexes vs multiple field indexes

Question

Currently we have a table that we use to track inivitations. We have an email field that is indexed but we also have three optional keys that the user can specify when adding new record emails. We don't allow duplicates so we have to query if the email plus the optional keys already exists. Currently the keys are only added to the select statement if they are specified. The normal case is only email is specified and using the index it works fairly quickly. When the keys are added performance drops.

Would adding three indexes affect performance for other operations? Keys are probably used infrequently that we wouldn't want to impact performance for this case.

• email, key1
• email, key1, key2
• email, key1, key2, key3

The other idea is we add 1 key.

• email, key1, key2, key3

Then always use all 3 keys in the lookup (eg. key1 = mykey AND key2 is NULL AND key3 is NULL)

See Also

Exact duplicate post

Answer 1

It depends on how often the table is updated, and how complex the indexes are. If you go nuts creating indexes, then every time a record is inserted/updated/deleted, every index will have to be modified to reflect that information.

If you only put three indexes on, and they are relatively simple, then you shouldn't have a problem.

Answer 2

Personally I would recommend this approach.

Try the method with the single index that covers everything, if I recall correctly it will still perform well if you only query on the first of the included columns. Once you have the index in place, run the Index Advisor.

Then try the other route and repeat.

It really depends on your data.

I typically have been able to get by with 1 covering index, starting with the most frequently used key first.

Answer 3

I could be wrong, but I believe if you add:

• email, key1, key2, key3

as an index, that most databases will use it if your query is using "email", "email/key1", "email/key1/key2", etc... without requiring you to specify Null values for the missing fields.

Answer 4

As others have said, most databases will use the index "a, b, c" when searching for just a, a and b or a, b and c. And they will often only use one index per table. So adding "email, key1, key2, key3" will probably be the best.

That said, use EXPLAIN to find out what's really going on. Check to make sure what indexes, if any, your queries are using. Every database has its quirks.