How to optimize Core Data query for full text search

I made a workaround solution. I think it's similar to this post. I added the amalgamation source code to my Core Data project, then created a full-text search class that was not a managed object subclass. In the FTS class I #import "sqlite3.h" (the source file) instead of the sqlite framework. The FTS class saves to a different .sqlite file than the Core Data persistent store.

When I import my data, the Core Data object stores the rowid of the related FTS object as an integer attribute. I have a static dataset, so I don't worry about referential integrity, but the code to maintain integrity should be trivial.

To perform FTS, I MATCH query the FTS class, returning a set of rowids. In my managed object class, I query for the corresponding objects with [NSPredicate predicateWithFormat:@"rowid IN %@", rowids]. I avoid traversing any many-to-many relationships this way.

The performance improvement is dramatic. My dataset is 142287 rows, comprising 194MB (Core Data) and 92MB (FTS with stopwords removed). Depending on the search term frequency, my searches went from several seconds to 0.1 seconds for infrequent terms (<100 hits) and 0.2 seconds for frequent terms (>2000 hits).

I'm sure there are myriad problems with my approach (code bloat, possible namespace collisions, loss of some Core Data features), but it seems to be working.

To follow up on this question, I've found that querying is dog slow using Core Data. I've scratched my head on this one for many hours.

As in the SQL example in my question, there are two entities: textTable and words where words contains each word, it is indexed, and there is a many-to-many relationship between textTable and words. I populated the database with a mere 4000 words and 360 textTable objects. Suppose the textTable relationship to the words object is called searchWords, then I can use a predicate on the textTable entity that looks like

predicate = [NSPredicate predicateWithFormat:@"ANY searchWords.word BEGINSWITH %@", query];

(I can add conjunctions of this predicate for multiple query terms.)

On the iPhone this query takes multiple seconds. The response for my hand-coded SQL using a larger test set was instant.

But this isn't even the end of it. There are limitations to NSPredicate that make rather simple queries slow and complex. For example, imagine in the above example that you want to filter using a scope button. Suppose the words entity contains all words in all text fields, but the scope would limit it to words from specific fields. Thus, words might have a "source" attribute (e.g. header and message body of email).

Naturally, then, a full text would ignore the source attribute, as in the example above, but a filtered query would limit the search to a particular source value. This seemingly simple change requires a SUBQUERY. For example, this doesn't work:

ANY searchWords.word BEGINSWITH "foo" AND ANY searchWords.source = 3

because the entities that are true for the two expressions can be different. Instead, you have to do something like:

SUBQUERY(searchWords, $x, $x.word BEGINSWITH "foo" AND $x.source = 3).@count > 0

I've found that these subqueries are, perhaps not surprisingly, slower than predicates using "ANY".

At this point I'm very curious how Cocoa programmers efficiently use Core Data for full text search because I'm discouraged by both the speed of predicate evaluation and expressibility of NSPredicates. I've run up against a wall.

Dive in.

Here's one way to go about it:

1. Put your records into a Core Data persistent store
2. Use NSFetchedResultsController to manage a result set on your Word entities (Core Data equivalent with SQL "words" table)
3. Use UISearchDisplayController to apply an NSPredicate on the result set in real time

Once you have a result set via NSFetchedResultsController, it is quite easy to apply a predicate. In my experience it will be responsive, too. For example:

if ([self.searchBar.text length]) {    _predicate = [NSPredicate predicateWithFormat:[NSString stringWithFormat:@"(word contains[cd] '%@')", self.searchBar.text]];    [self.fetchedResultsController.fetchRequest setPredicate:_predicate];}NSError *error;if (![self.fetchedResultsController performFetch:&error]) {    // handle error...}NSLog(@"filtered results: %@", [self.fetchedResultsController fetchedObjects]);

will filter the result set [self.fetchedResultsController fetchedObjects] on the fly, doing a case-insensitive search on word.