Page Comparison

...

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

Returns

Full table scans are really slow, especially when querying a large data set.

...

1. *Application.java →  Add the register table names

   Code Block
   language java theme Eclipse
   private String[] getFtsTables(){ String[] ftsTables = { "household" ... }; 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. Add condition fields If your query has a main 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 *Application.onCreate(), update the global context instance i.e. update context.commonFtsObject

   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 org.ei.opensrp.Context.java, 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 in org.ei.opensrp.repository.Repository#onCreate, 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); } }

6. These virtual tables are updated after saving a form submission.

   For Example, In in FormSubmissionService class.java:

   Code Block
   language java
   ziggyService.saveForm(getParams(submission), submission.instance()); // Update FTS Tables updateFTSsearch(submission);

7. Update filterandSortExecute() and CountExecute() methods to use FTS search

   1. Perform search/sort query in the virtual table(s) and return the reference ids

      Code Block
      language sql
      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:

      Virtual table full scans are optimized
      
      

   2. Use the ids in 7(a) above to filter the main query

      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:

      
      

   3. 
      
   
   
8. 
   

Facts

1. Queries perform much faster when indexes are used.
2. Indexes are ignored when SQLite's LIKE-keyword based query is used.
3. Virtual tables require more space compared to regular tables.
4. It takes more time to insert a record in a virtual table.
5. Always consider explain query plan to determine whether your queries aren’t going to work slowly.
6. FTS does not support suffix search, only prefix.

...