Page Comparison

...

Code Block

language	sql

explain query plan select * from mcaremother where FWWOMFNAME LIKE '%abc%'

Returns

selectid	order	from	detail
0	0	0	SCAN TABLE mcaremother

Full table scans are really slow, especially with a large data set. They need to be optimized

...

In your Application class, add the register table names that need to use FTS search

Code Block

language	java
theme	Eclipse

private String[] getFtsTables(){
    String[] ftsTables = { "household", "elco", "mcaremother", "mcarechild" };
    return ftsTables;
}

Add search and sort fields

Code Block

language	java
theme	Eclipse

private String[] getFtsSearchFields(String tableName){
    if(tableName.equals("household")){
        String[] ftsSearchFileds =  { "FWHOHFNAME", "FWGOBHHID", "FWJIVHHID" };
        return ftsSearchFileds;
    } else if(tableName.equals("elco")){
        String[] ftsSearchFileds =  { "FWWOMFNAME", "GOBHHID", "JiVitAHHID" };
        return ftsSearchFileds;
    } else if (tableName.equals("mcaremother")){
        String[] ftsSearchFileds =  { "FWWOMFNAME", "GOBHHID", "JiVitAHHID", "Is_PNC" };
        return ftsSearchFileds;
    } else if (tableName.equals("mcarechild")){
        String[] ftsSearchFileds =  { "FWWOMFNAME", "GOBHHID", "JiVitAHHID" };
        return ftsSearchFileds;
 
  }

   return null;
}

private String[] getFtsSortFields(String tableName){
    if(tableName.equals("household")) {
        String[] sortFields = {"FWHOHFNAME", "FWGOBHHID", "FWJIVHHID"};
        return sortFields;
    } else if(tableName.equals("elco")){
        String[] sortFields = {"FWWOMFNAME", "GOBHHID", "JiVitAHHID"};
        return sortFields;
    } else if(tableName.equals("mcaremother")){
        String[] sortFields = {"FWWOMFNAME", "GOBHHID", "JiVitAHHID", "FWPSRLMP", "FWBNFDTOO", "FWSORTVALUE"};
        return sortFields;
    } else if(tableName.equals("mcarechild")){
        String[] sortFields = {"FWWOMFNAME", "GOBHHID", "JiVitAHHID", "FWSORTVALUE"};
        return sortFields;
   
}     return null;
}

If your query has a main condition, add the fields in this condition

For Example:

Code Block

language	java

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

Code Block

language	java
theme	Eclipse

private String[] getFtsMainConditions(String tableName){
    if(tableName.equals("household")) {
        String[] sortFields = {"FWHOHFNAME"};
        return sortFields;
    } else if(tableName.equals("elco")){
        String[] sortFields = {"FWWOMFNAME", "details"};
        return sortFields;
    } else if(tableName.equals("mcaremother")){
        String[] sortFields = {"FWWOMFNAME", "Is_PNC", "details"};
        return sortFields;
    } else if(tableName.equals("mcarechild")){
        String[] sortFields = {"FWBNFGEN"};
        return sortFields;
    }
    return null;
}

In onCreate() update the global context instance to have the values specified above.

Code Block

language	java

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));
    }
    return commonFtsObject;
}

In the Context class, commonFtsObject is passed as an argument when initializing the Repository class.

Code Block

language	java

if(commonFtsObject != null){
    repository = new Repository(this.applicationContext, session(), this.commonFtsObject, drishtireposotoryarray);
}

Then the virtual tables are created using fts4, after creating the database,

org.ei.opensrp.repository.Repository#onCreate

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.
For Example, In FormSubmissionService class:
Code Block
language java
ziggyService.saveForm(getParams(submission), submission.instance()); // Update FTS Tables updateFTSsearch(submission);
s

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.

...

Versions Compared

Old Version 3

New Version 4

Key