Single vs Multiple Field Indexes

Mobile
,
1

Context:

In Couchbase Lite (via the Swift SDK), suppose I have model objects like this:

class Song
{
    var title: String
    var normalizedTitle: String

    var artist: String
    var normalizedArtist: String

    var durationInSeconds: Int
}

Where the normalized version of certain properties have been forced to lowercase, stripped of diacritics, and standardized for multi-byte characters so that I can perform an exact equality test rather than a case-insensitive search.

I need to query Song objects that match (exactly) a given title, artist, and duration. So I create an index on normalizedTitle, normalizedArtist, and durationInSeconds.

Question:

That same index will cover cases where I query for any one of the fields included in the index, correct? So if I want to match for just title, the index will still be used even though it includes other fields?

Is there any advantage to having an additional single-field index if a multi-field index already exists?

Does the order of the fields in the multi-field index matter when I create the index? (For example, is it better to create the index with durationInSeconds as the first field because that’s a faster value to test than potentially long strings for the other two fields?)

I understand the general guidance about “don’t create indexes needlessly; they slow down writes.” But is there practical advice for an upper limit on the number of fields in a single index?

2

I’m not the right guy to answer this, but.. I think while a compound index of (a,b,c) would work well for predicates on only ‘a’, it would not work well for predicates on ‘b’ or ‘c’ without ‘a’ . The index lookups rely on the indexes being ordered, so that works for ‘a’, but does not help with ‘b’ or ‘c’ without ‘a’. For your case, I would guess that three separate indexes would be best for your case. I find it helpful to use the query workbench (ui) to investigate queries and indexes and query plans.

1 Like