Page Comparison

...

Code Block

language	sql

explain query plan select * from mcaremotherhousehold where FWWOMFNAMEFWHOHFNAME LIKE '%abc%'

Returns

selectid	order	from	detail
0	0	0	SCAN TABLE mcaremotherhousehold

Full table scans are really slow, especially when querying a large data set. Consider optimizing such queries.

Adopting FTS search

*Application.java → Add the register table names

Code Block

language	java
theme	Eclipse

private String[] getFtsTables(){
    String[] ftsTables = { "householdtable1" ... };
    return ftsTables;
}

Add search and sort fields

Code Block

language	java
theme	Eclipse

private String[] getFtsSearchFields(String tableName){
    if(tableName.equals("householdtable1")){
        String[] ftsSearchFiledsftsSearchFields =  { "FWHOHFNAMESearchField1", "FWGOBHHID", "FWJIVHHID" ... };
        return ftsSearchFiledsftsSearchFields;
    } ...
}

private String[] getFtsSortFields(String tableName){
    if(tableName.equals("householdtable1")) {
        String[] sortFields = {"FWHOHFNAME", "FWGOBHHID", "FWJIVHHID"}SortField1" ... };
        return sortFields;
    } ...
    return null;
}

Add custom relational Ids if they exist.

These are foreign keys apart from relationalId or relational_id fields that store a unique identifier for the related table e.g. childId or parentId

Code Block

language	java
theme	Eclipse

private String getFtsCustomRelationalId(String tableName){
    if(tableName.equals("table1")){
        String customRelationalId = "parentId1";
        return sortFieldscustomRelationalId;
    } ...
    return null;
}

Add condition fields If your if the query has a main condition

For Example:

Code Block

language	java
theme	Eclipse

queryBUilder.mainCondition(" FWHOHFNAMEField1 is not null ");

Code Block

language	java
theme	Eclipse

private String[] getFtsMainConditions(String tableName){
    if(tableName.equals("householdtable1")) {
        String[] sortFieldsmainConditions = {"FWHOHFNAMEField1"};
        return sortFieldsmainConditions;
    } ...
}

In *Application.onCreate(), update the global context instance i.e. update context.commonFtsObject

Code Block

language	java
theme	Eclipse

context.updateCommonFtsObject(createCommonFtsObject());

Code Block

language	java
theme	Eclipse

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));
		commonFtsObject.updateCustomRelationalId(ftsTable, getFtsCustomRelationalId(ftsTable));
 	}
    return commonFtsObject;
}

In org.ei.opensrp.commonregistry.CommonRepository#onCreate, id and relationalid indexes are created for each common repository table.

Code Block

language	java
theme	Eclipse

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);
}
 return commonFtsObjectif(StringUtils.isNotBlank(common_Custom_Relational_ID_INDEX_SQL)) {
    database.execSQL(common_Custom_Relational_ID_INDEX_SQL);
}

In org.ei.opensrp.Context.java, commonFtsObject is passed as an argument when initializing the Repository class.

Code Block

language	java
theme	Eclipse

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 , just after the database has been created.

Code Block

language	java
theme	Eclipse

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 is saved.
For Example, In in FormSubmissionService class.java:
Code Block
language java
theme Eclipse
ziggyService.saveForm(getParams(submission), submission.instance()); // Update FTS Tables updateFTSsearch(submission);
Update filterandSortExecute() and CountExecute() methods to use FTS search
1. Perform search/sort query in the virtual table(s) and return the reference ids
  Virtual table scans are optimized and therefore faster than normal full table scans.
  Code Block
  language sql
  title Explain query plan
  explain 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:
2. Use the ids returned in Step 9(a) above to filter the main query
  Since id fields are indexed, Step 6, full table scans are replaced with search table using index.
  Code Block
  language sql
  title Explain query plan
  explain 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:

Facts

Queries perform much

Step 9 a) and b) in code

Code Block

language	java
theme	Eclipse
title	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

Code Block

language	sql

PRAGMA table_info('household_search');

Returns

cid	name	type	not null	`dflt_value`	pk
0	object_id		0		0
1	object_relational_id		0		0
2	phrase		0		0
3	SortField1		0		0
4	SortField2		0		0
5	SortField3		0		0

object_id → reference id i.e. household.id

object_relational_id → reference relational id i.e. household.relationalid. Useful when query has a join between two register tables.

phrase → search term, contains all the searchable values separated by ' | ' e.g. " searchFieldValue1 | searchFieldValue2 | searchFieldValue3 "

sort field → sort value

Facts

Queries are faster when indexes are used.
Indexes are ignored when SQLite's LIKE-keyword based query is used.
Virtual tables require more disk 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.

...

Versions Compared

Old Version 6

New Version Current

Key

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: