NSFetchRequest predicate with BOOL check is SLOW

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

---

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.

---

I ended up taking advice on @pothibo and Ivo Jansch's answer 2nd approach and do the following:

1. When the user types in the first letter in the field, I fetch all the entries that start with that letter (so basically calling that autocompleteSuggestions: method but without setting the fetch request's fetchLimit property)
2. Every time the user adds or remove a letter, apply an appropriate NSPredicate to that initially fetched array using filteredArrayUsingPredicate:.

This results in a slighly slower initial request (although still < 1s) but lightning-fast subsequent fetches.

This is a really clever way to do autocomplete suggestions, since the new set of autocomplete suggestions is always going to be a subset of the previous one. Thanks @pothibo!

---

CoreData isn't' really doing a whole lot here between those two queries. There are two possible causes for this slowdown, both can be diagnosed by setting -com.apple.CoreData.SQLDebug 3 on your application launch arguments in Xcode.

1. Your slow query is hitting two indexes and for some reason this is slow in SQLite
2. Your slow query's SQL is not generated correctly

Solutions:

1. Update to iOS 5 Beta 3 and use multi-column indexing in your data model to index your hidden and name attributes
2. bugreport.apple.com with your application's data store and SQL debug log for both queries.