NSFetchRequest predicate with BOOL check is SLOW

Question

Consider the following 2 predicates filtering through a 5k+ entries store:

predicate1 = [NSPredicate predicateWithFormat:@"hidden == NO AND name BEGINSWITH[cd] %@", searchString];
predicate2 = [NSPredicate predicateWithFormat:@"name BEGINSWITH[cd] %@", searchString];

I turned on -com.apple.CoreData.SQLDebug to see the fetch request times:

predicate1: 0.4728s
predicate2: 0.0867s

Am I missing something? Both columns are indexed. How come adding a simple boolean check slows down the fetch request that much?

EDIT: as requested, the output:

CoreData: sql: SELECT 0, t0.Z_PK, t0.Z_OPT, t0.ZHIDDEN, t0.ZID, t0.ZNAME, t0.ZRANK FROM ZARTISTINDEX t0 WHERE ( t0.ZHIDDEN = ? AND ( NSCoreDataStringSearch( t0.ZNAME, ?, 393, 0) OR  NSCoreDataStringSearch( t0.ZNAME, ?, 393, 0))) ORDER BY t0.ZRANK DESC LIMIT 14

That rank column is also indexed. The reason I need this request to be faster than 0.5s is that it's used for an autocomplete feature. That request is made every time the value of some text field gets changed by the user.

EDIT 2: adding more contextual info:

- (NSArray*)autocompleteSuggestions:(NSString*)searchString {

    NSFetchRequest *request = [[NSFetchRequest alloc] init];
    NSEntityDescription *entity = [NSEntityDescription entityForName:@"ArtistIndex" inManagedObjectContext:self.indexObjectContext];
    [request setEntity:entity];
    [request setFetchLimit:10];

    NSPredicate *predicate = [NSPredicate predicateWithFormat:@"hidden == NO AND (name BEGINSWITH[cd] %@ OR name BEGINSWITH[cd] %@)", searchString, [NSString stringWithFormat:@"the %@", searchString]];
    [request setPredicate:predicate];

    NSSortDescriptor *sortDescriptor = [[NSSortDescriptor alloc] initWithKey:@"rank" ascending:NO];
    [request setSortDescriptors:[NSArray arrayWithObject:sortDescriptor]];
    [sortDescriptor release];

    NSArray *resultsArray = [self.indexObjectContext executeFetchRequest:request error:nil];
    [request release];

    return resultsArray;
}

The ArtistIndex entity has the following attributes:

• id (int32)
• name (string, indexed)
• rank (int32, indexed)
• hidden (BOOL, indexed)

Edit 3: here are the full SQL outputs for the slow query (predicate1) and the fast query (predicate2) with com.apple.CoreData.SQLDebug set to 3. More rigorous testing brought me the following test times, which are better, but still have a +2x difference and really make a difference in an autocomplete suggestions context. Or is this a reasonable fetch time difference now?

predicate1: 0.3772s
predicate2: 0.1633s

Answer 1

With a where clause on 2 columns and indexes on only 1, sqlite is probably deciding to use the index on the boolean field. That leads to a fast select, but it will result internally in having half your data set loaded; then it starts to do the other clause, but it needs to do that by looking at every record. That's why the boolean search is causing the slowdown.

I think there are 3 approaches you should try.

1. Queries on 2 columns will perform poor unless there is one index on both columns. I don't believe Core Data lets you index multiple columns in a single index yet, but the underlying SQLite database does. So just to see if this is what's causing the issue, try to manually create an index on your sqlite database on both columns. It is important that your boolean value is the second column in the index, as it's not distinctive enough (it only has 2 values so the data set it needs to filter is half your database; this makes indexing slightly less efficient).

2. Add a utility column that contains a concatenation of the name and hidden fields and have that indexed and search on that. This might be less effective though since you're matching substrings.

3. If the amount of records with hidden=YES is small, just leave it out of the predicate and filter them afterwards.