Indexing Virtual Fields

In general, SQL servers support limited indexing capabilities. Indexes can be maintained on individual columns (multi-valued or unique) and also on the concatenation of two or more columns (multi-valued or unique). In Zim terms, an indexed virtual field that corresponds to an SQL concatenated index can be represented only by an expression of the form:

$concat(field1,field2,…)

where “field1”, “field2” etc. are the names of fields only. No other expression operators are permitted.

This means that indexed virtual fields defined by such expressions can be mapped to indexes in the SQL database that are defined as the concatenation of two or more columns. The multi-valued/unique options for such indexes can also be mapped to SQL.

The Zim Definition Interface supports the export of indexed virtual fields whose associated field expressions conform to the simple concatenation structure indicated above. Performance improvements can be achieved from the SQL server for virtual field indexes so defined. Indexed virtual field expressions of any other form are not supported in SQL and accordingly, no performance improvements can be achieved. For applications whose design rests on the performance resulting from indexed virtual fields which do not conform to the above format (e.g. substrings of fields), test the application as it exists. If performance is not acceptable, then the virtual fields must be implemented as additional real fields, necessitating the creation of additional indexed columns to the corresponding SQL table(s).