Indexing and Search

When a field is indexed its data is copied to a table and optimized for searching; when you search an indexed field, you search the optimized index rather than the field itself. There is more to indexing than that but the key point is that indexing a field makes a search in the field quick and efficient (more details can be found in the Axiell Designer Help).

Not all fields in Collections are indexed. When you search a data source The management of a collection can involve a vast amount of information about objects / items / books, people and organizations, events, administration and more. This information is stored as records in data sources. Each data source stores a specific type of information: details about collection items, people, events, loans, and so on. using the Standard tab of the Search box, only indexed fields are made available for searching. On the Advanced tab it is possible to search non-indexed fields by selecting Settings and enabling Show non-indexed fields too, however searching a non-indexed field can be slow (a sequential search of the data across all records is performed).

When running a search some awareness of how a field might be indexed can be useful. Short of accessing the Collections data dictionary in Axiell Designer A tool for designing, creating, customizing and managing Axiell Collections applications and databases, broadly speaking, the Axiell Collections Model Application. As well as managing databases, including user access and permissions, Designer is used for such tasks as translating field labels, tooltips, values in drop lists, etc., there is no simple way to know precisely how a field has been indexed, but the following guidelines, and testing with your data, will help you understand and optimize your search queries. By experimenting with different search terms and observing the results, you can gain insights into how indexing works and adjust your queries accordingly to achieve more accurate and relevant results.

For our purposes, a field can be indexed in one of three ways: the entire contents of the field is indexed as a single value (Text (term)) or each word in a field is indexed separately (Free text). An optional, third type of indexing, Full Text indexing, is also available (details below).

Details

Index	Details
Text (term)	The entire value in the field is indexed as a single value, including spaces and punctuation. Linked fields A type of field used to link one record to another. A Linked field is a drop list of values (records that the field can link to). When a link is made, the field stores a reference to the linked record (a linkref). such as Object name (object_name (OB)) and Creator (creator (VV)) are indexed in this way. The search value must exactly match the entire contents of the field. If it includes a space, hyphen / minus (`-`) or plus (`+`), the entire search value must be enclosed in quotes. For example, if values are stored in the creator (VV) field as surname, firstname, your search statement would be constructed as: creator = "wood, gerard" This is where the `` wildcard can be extremely useful. The `` wildcard substitutes zero or more characters at its position in a search value. For example: creator = wood,* will return any record for creators with the last name `wood` (assuming creator names are formatted as surname, firstname). Rather than using the `` wildcard, you could use the contains* search operator.
Free text (aka Word indexed field)	Each word in a field is indexed separately. A search value must match exactly with a value found somewhere in the searched field. Free text fields (notes, description and book titles for instance) are typically indexed in this way. As each word is indexed separately, you can include multiple values in any order. However all specified values must be matched for a record to be returned by this search. When searching for more than one word, enclose the entire search value in quotes. Note: A Boolean AND search applies when including more than one value in the search field: every value must be found in order for a record to be returned by the search. For example, any title that includes the two words the and and in any order will be matched by this search: title = "the and" If a title includes the but not and, it will not be returned by this search. Each search value must match exactly with a value found in the searched field: the will match the but not other for instance. Again, the `` wildcard can be employed. In this case, the* will match the, other, their, etc.: title = "the and" Notes Punctuation enclosed by spaces (i.e. with a space on either side) or quotes (double or single) around words in your record data are not indexed and therefore cannot be searched for with equals / is. To search for them, use contains. When using the `` wildcard to truncate a search value in a long text field, your search value will only be located if it is in the first 100 characters of the field. Instead, use the contains* / contains all search operator (an `_`in an Advanced search) as this limitation does not apply.

Index

Details

Text (term)

The entire value in the field is indexed as a single value, including spaces and punctuation. Linked fields A type of field used to link one record to another. A Linked field is a drop list of values (records that the field can link to). When a link is made, the field stores a reference to the linked record (a linkref). such as Object name (object_name (OB)) and Creator (creator (VV)) are indexed in this way.

The search value must exactly match the entire contents of the field. If it includes a space, hyphen / minus (-) or plus (+), the entire search value must be enclosed in quotes.

For example, if values are stored in the creator (VV) field as surname, firstname, your search statement would be constructed as:

creator = "wood, gerard"

This is where the * wildcard can be extremely useful. The * wildcard substitutes zero or more characters at its position in a search value. For example:

creator = wood,*

will return any record for creators with the last name wood (assuming creator names are formatted as surname, firstname).

Rather than using the * wildcard, you could use the contains search operator.

Free text

(aka Word indexed field)

Each word in a field is indexed separately. A search value must match exactly with a value found somewhere in the searched field. Free text fields (notes, description and book titles for instance) are typically indexed in this way.

As each word is indexed separately, you can include multiple values in any order. However all specified values must be matched for a record to be returned by this search.

When searching for more than one word, enclose the entire search value in quotes.

Note: A Boolean AND search applies when including more than one value in the search field: every value must be found in order for a record to be returned by the search.

For example, any title that includes the two words the and and in any order will be matched by this search:

title = "the and"

If a title includes the but not and, it will not be returned by this search.

Each search value must match exactly with a value found in the searched field: the will match the but not other for instance.

Again, the * wildcard can be employed. In this case, *the* will match the, other, their, etc.:

title = "*the* and"

Notes

Punctuation enclosed by spaces (i.e. with a space on either side) or quotes (double or single) around words in your record data are not indexed and therefore cannot be searched for with equals / is. To search for them, use contains.
When using the * wildcard to truncate a search value in a long text field, your search value will only be located if it is in the first 100 characters of the field.
Instead, use the contains / contains all search operator (an _in an Advanced search) as this limitation does not apply.

Rule of thumb

A Free Text field (notes, description and book titles for instance) can be searched for values found in the field, a single word for instance; when searching a Text (term) field, notably Linked fields A type of field used to link one record to another. A Linked field is a drop list of values (records that the field can link to). When a link is made, the field stores a reference to the linked record (a linkref)., your search value must exactly match the entire contents of the field.

In either case, the * wildcard can be extremely useful. The * substitutes zero or more characters at its position in a search value, so rather than searching for "Wood, Gerard" in a Linked field, a search for:

creator = wood,*

will return any record for creators with the last name wood (assuming creator names are formatted as surname, firstname).

Full text indexing

Full Text indexing was introduced with Axiell Designer A tool for designing, creating, customizing and managing Axiell Collections applications and databases, broadly speaking, the Axiell Collections Model Application. As well as managing databases, including user access and permissions, Designer is used for such tasks as translating field labels, tooltips, values in drop lists, etc. 7.8 and Collections 1.14. It is optional, but when implemented in a data source The management of a collection can involve a vast amount of information about objects / items / books, people and organizations, events, administration and more. This information is stored as records in data sources. Each data source stores a specific type of information: details about collection items, people, events, loans, and so on.:

A single Full Text index table per .inf file replaces all Free Text index tables, all non-unique Text (term) index tables and the wordlist table, with the result that SQL queries are simpler and more efficient.
Search performance in long text fields, fields with unique terms, fields with non-unique terms and Linked fields A type of field used to link one record to another. A Linked field is a drop list of values (records that the field can link to). When a link is made, the field stores a reference to the linked record (a linkref). can be improved. In such fields:
- Phrase searching is available. The contains phrase and does not contain phrase search operators are available in a Standard search; contains phrase is available in an Advanced search 1.
- The starts with operator is available in a Standard and Advanced2 search. starts with is useful when searching long text fields as it allows you to search for the first word in the field, in contrast to equals, which searches for your search term anywhere in the field.
- The containsany and containsall search operators are available in an Advanced search 3.
Search performance can be improved where values include special characters (commas, hyphens, brackets, etc.) as these characters or combinations of characters can be omitted 4.

A single Full Text index table

As we state above, implementing Full Text indexing results in a single Full Text index table per .inf file replacing all Free Text index tables, all non-unique Text (term) index tables and the wordlist table.

While true, this is a simplification. Although the Free Text and Text (term) index tables are replaced with a single Full Text index table, the Free Text and Text (term) definitions remain in the .inf file for a database table: amongst other things, the .inf file specifies whether a term is checked for uniqueness during indexing.

Furthermore, while this results in a single Full Text index table per .inf, it doesn't mean there is a single index; in fact multiple indexes are maintained in a Full Text index table. This matters because different search operators query different indexes in a Full Text index table and therefore may give different results.

It is not critical to understand what follows as we explain in the Search pages exactly how each search operator works and what results you can expect; however you might find it useful as it explains why you might get different search results when searching the data with different search operators.

Here we see an extract of a Full Text index table, with values from the Title (Ti) field:

The actual value found in the Title field is in the value column; three indexes are available, stripped, term and strippedterm. To understand how a field's value is indexed, consider the highlighted value for the record with priref A record's unique identifier (aka Record number). The Record number field is found on the Management details (or Notes and description) panel in Collections version 5.0 onwards. Alternatively, add the priref column to Result set View. The field tag for the priref field is %0. 72: Vincent in Saint-Rémy : de rijpe schilder van de bezonken kleur; mei 1889-mei, and note that there are two other records with Vincent in Saint-Rémy in the title (priref73 and 74).

The stripped column is added to the index table when Full text indexing is implemented; the name reflects the stripping of punctuation, diacritics and special characters from the indexed value. As we can see, a value without such characters does not get a stripped value (e.g. priref 69). The stripped value for priref 72 is:

vincent in saintremy de rijpe schilder van de bezonken kleur mei 1889me

So how might this impact search results?

When Full Text indexing is implemented, a contains search (and its variations) queries the stripped column for your search term. Thus:

title _ "Vincent in Saint-Rémy"

will return three records as case, punctuation (notably the hyphen) and accents are all ignored.

A starts with search however queries the term index column. Thus:

title startswith "Vincent in Saint-Rémy"

will only return two records as Vincent in Saint Rémy is not matched (diacritics are ignored by default in Collections, but separators are not and the indexed term value includes the hyphen).

Tip: It is possible to search for special characters (including diacritics). Details here.

So much for the theory of indexing. Keep in mind that the critical thing to understand about a search operator like contains is that it is case and diacritic insensitive and it ignores separators (like hyphens); this aspect of a search operator is documented where we describe search operators and that is the practical information you need when performing a search.

However, it's worth stressing the value of experimenting with different search terms and observing the results as this will provide insights into how indexing works and will help you adjust your queries to achieve more accurate and relevant results.