NSFetchRequest predicate with BOOL SLOW validation

Question

Consider the following 2 predicates that are filtered through the 5k + record store:

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

I included -com.apple.CoreData.SQLDebug to see the fetch request time:

predicate1: 0.4728s
predicate2: 0.0867s

Am I missing something? Both columns are indexed. Why does adding a simple boolean check slow down a fetch request?

EDIT : on request, 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 

The rank column is also indexed. The reason I need this query to be faster than 0.5 s is because it is used for the autocomplete function. This query is executed every time a custom value changes the value of a certain text field.

EDIT 2 : adding more contextual information:

 - (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 object 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 fast query (predicate2) with com.apple.CoreData.SQLDebug set to 3. More stringent tests led me to the following test times, which are better, but still have a difference of + 2x and really matter in the context of autocomplete suggestions. Or is it now a reasonable time difference?

predicate1: 0.3772s
predicate2: 0.1633s

Answer 1

I ended up advising @pothibo , and Ivo Jansch answered the second approach and did the following:

• When the user enters the first letter in the field, I retrieve all the entries that start with this letter (therefore basically calling this method autocompleteSuggestions: but without setting the fetchLimit fetch fetchLimit )
• Each time a user adds or removes a letter, apply the appropriate NSPredicate to the initially selected array using filteredArrayUsingPredicate:

This results in a slower initial query (although still <1s), but faster subsequent retrieval.

This is a really smart way to make autocomplete suggestions, as the new set of autocomplete suggestions will always be a subset of the previous one. Thanks @pothibo!

Answer 2

With a where clause on 2 columns and only 1 indexes, sqlite will probably decide to use the index in a boolean field. This leads to quick selection, but it will cause the inside to be half filled with your data set; then he begins to fulfill another sentence, but he needs to do this by looking at each entry. That is why logical search causes a slowdown.

I think you should try 3 approaches.

• Queries on 2 columns will work poorly if there is no single index in both columns. I do not believe that Core Data allows you to index multiple columns in a single index, but the underlying SQLite database. Therefore, to verify that this is causing the problem, try manually creating an index in the sqlite database on both columns. It is important that your boolean is the second column in the index, as it is not distinctive enough (it has only 2 values, so the data set that needs to be filtered is half the database, which makes indexing a little less efficient).

• Add a utility column containing the concatenation of the name and hidden fields and index it and search on it. This may be less efficient though, since you match the substrings.

• If the number of entries with hidden = YES is small, just leave it outside the predicate and then filter it.

Answer 3

CoreData doesn't do much here between these two queries. There are two possible reasons for this slowdown, both can be diagnosed by setting -com.apple.CoreData.SQLDebug 3 in the application startup arguments in Xcode.

• Your slow query hits two indexes, and for some reason it's slower in SQLite
• Your slow SQL query is not generated correctly

Solutions:

• Update iOS 5 Beta 3 and use multiple column indexing in your data model to index your hidden attributes and names.
• bugreport.apple.com with your application datastore and SQL debug log for both queries.