Page Comparison

...

Before adding FTS search, Always always consider explain query plan to determine whether your queries will be slow when the table has more records.

For Example:

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

...

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

Consider optimizing

Adopting FTS search

1. In your Application class, add *Application.java →  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; }

2. 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; } ... } private String[] getFtsSortFields(String tableName){ if(tableName.equals("household")) { String[] sortFields = {"FWHOHFNAME", "FWGOBHHID", "FWJIVHHID"}; return sortFields; } ... return null; }

3. 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; } ... }

4. 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; }

5. 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); } }

6. 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);

7. s

...