FTS3 and FTS4 are SQLite virtual table modules that allows users to perform full-text searches on a set of documents.
The basic idea is to have a virtual FTS table for each register table.
The virtual table holds the values that are used for searching and sorting as well as an identifier to link the register table.
Prerequisite
Always consider explain query plan
to determine whether your queries will be slow.
For Example:
explain query plan select * from household where FWHOHFNAME LIKE '%abc%'
Returns
selectid | order | from | detail |
---|---|---|---|
0 | 0 | 0 | SCAN TABLE household |
Full table scans are really slow, especially when querying a large data set.
Consider optimizing
Adopting FTS search
*Application.java → Add the register table names
private String[] getFtsTables(){ String[] ftsTables = { "table1" ... }; return ftsTables; }
Add search and sort fields
private String[] getFtsSearchFields(String tableName){ if(tableName.equals("table1")){ String[] ftsSearchFields = { "SearchField1" ... }; return ftsSearchFields; } ... } private String[] getFtsSortFields(String tableName){ if(tableName.equals("table1")) { String[] sortFields = {"SortField1" ... }; return sortFields; } ... return null; }
Add condition fields If your query has a main condition
For Example:
queryBUilder.mainCondition(" Field1 is not null ");
private String[] getFtsMainConditions(String tableName){ if(tableName.equals("table1")) { String[] mainConditions = {"Field1"}; return mainConditions; } ... }
In *Application.onCreate(), update the global context instance i.e. update context.commonFtsObject
context.updateCommonFtsObject(createCommonFtsObject());
private CommonFtsObject createCommonFtsObject(){ CommonFtsObject commonFtsObject = new CommonFtsObject(getFtsTables()); for(String ftsTable: commonFtsObject.getTables()){ commonFtsObject.updateSearchFields(ftsTable, getFtsSearchFields(ftsTable)); commonFtsObject.updateSortFields(ftsTable, getFtsSortFields(ftsTable)); commonFtsObject.updateMainConditions(ftsTable, getFtsMainConditions(ftsTable)); } return commonFtsObject; }
In org.ei.opensrp.commonregistry.CommonRepository#onCreate, id and relationalid indexes are created for each common repository table.
if(StringUtils.isNotBlank(common_ID_INDEX_SQL)) { database.execSQL(common_ID_INDEX_SQL); } if(StringUtils.isNotBlank(common_Relational_ID_INDEX_SQL)) { database.execSQL(common_Relational_ID_INDEX_SQL); }
In org.ei.opensrp.Context.java, commonFtsObject is passed as an argument when initializing the Repository class.
if(commonFtsObject != null){ repository = new Repository(this.applicationContext, session(), this.commonFtsObject, drishtireposotoryarray); }
Then the virtual tables are created in org.ei.opensrp.repository.Repository#onCreate, after the database has been created.
if(this.commonFtsObject != null) { for (String ftsTable: commonFtsObject.getTables()) { Set<String> searchColumns = new LinkedHashSet<String>(); searchColumns.add(CommonFtsObject.idColumn); searchColumns.add(CommonFtsObject.relationalIdColumn); searchColumns.add(CommonFtsObject.phraseColumnName); String[] mainConditions = this.commonFtsObject.getMainConditions(ftsTable); if(mainConditions != null) for (String mainCondition : mainConditions) { searchColumns.add(mainCondition); } String[] sortFields = this.commonFtsObject.getSortFields(ftsTable); if(sortFields != null) for (String sortValue : sortFields) { searchColumns.add(sortValue); } String joinedSearchColumns = StringUtils.join(searchColumns, ","); String searchSql = "create virtual table " + CommonFtsObject.searchTableName(ftsTable) + " using fts4 (" + joinedSearchColumns + ");"; database.execSQL(searchSql); } }
These virtual tables are updated after saving a form submission.
For Example, in FormSubmissionService.java:
ziggyService.saveForm(getParams(submission), submission.instance()); // Update FTS Tables updateFTSsearch(submission);
- Update filterandSortExecute() and CountExecute() methods to use FTS search
Perform search/sort query in the virtual table(s) and return the reference ids
Full Virtual table scans are optimized and therefore faster than normal table scans.
Explain query planexplain query plan select object_id from household_search where phrase match 'abc*'
Returns
selectid order from detail 0 0 0 SCAN TABLE household_search VIRTUAL TABLE INDEX 4: Use the ids in Step 7(a) above to filter the main query
Since id fields are indexed, Step 5, there are no full table scans, only virtual table scans.
Explain query planexplain query plan select * from household where id in (select object_id from household_search where phrase match 'abc*')
Returns
selectid order from detail 0 0 0 SEARCH TABLE household USING INDEX sqlite_autoindex_household_1 (id=?) 0 0 0 EXECUTE LIST SUBQUERY 1 0
0 0 SCAN TABLE household_search VIRTUAL TABLE INDEX 4:
Using FTS// Get the virtual table query String sql = sqb.searchQueryFts(tablename, joinTable, mainCondition, filters, Sortqueries, currentlimit, currentoffset); // Get the reference ids List<String> ids = commonRepository.findSearchIds(sql); // Update the main query by adding the reference ids in the where clause currentquery = sqb.toStringFts(ids, tablename + "." + CommonRepository.ID_COLUMN, Sortqueries); query = sqb.Endquery(currentquery);
FTS Table Structure
Facts
- Queries perform much faster when indexes are used.
- Indexes are ignored when SQLite's LIKE-keyword based query is used.
- Virtual tables require more space compared to regular tables.
- It takes more time to insert a record in a virtual table.
- Always consider
explain query plan
to determine whether your queries aren’t going to work slowly. - FTS does not support suffix search, only prefix.
Links
https://www.sqlite.org/fts3.html
https://lab.getbase.com/killer-search-on-android-part-1
http://stackoverflow.com/questions/29815248/full-text-search-example-in-android
0 Comments