AppSuite:MySQL Fulltext Index - Revision history

Tobias.friedrich: Replaced content with "{{Migration|title=MySQL Fulltext Index|link=https://documentation.open-xchange.com/7.10.2/middleware/administration/mysql_fulltext_index.html}}"

2019-04-26T10:41:43Z

Replaced content with "{{Migration|title=MySQL Fulltext Index|link=https://documentation.open-xchange.com/7.10.2/middleware/administration/mysql_fulltext_index.html}}"

← Older revision		Revision as of 10:41, 26 April 2019
Line 1:		Line 1:
	~~<div class="title">Using MySQL <code>FULLTEXT</code> index for Auto-Complete</div>~~		{{Migration\|title=MySQL Fulltext Index\|link=https://documentation.open-xchange.com/7.10.2/middleware/administration/mysql_fulltext_index.html}}

	{{~~VersionFrom~~\|~~7.8.1}}~~

	~~__TOC__~~


	~~== Introduction~~ ==

	With Open-Xchange Server v7.8.1 the MySQL <code>FULLTEXT</code> index is supported for retrieving auto-complete results. In order to use that capability an appropriate MySQL version needs to be in place. <code>FULLTEXT</code> indexes are supported starting with MySQL v5.6.4 (see http://dev.mysql.com/doc/refman/5.6/en/fulltext-restrictions.html for details).

	Once enabled, an appropriate index is created on the <code>prg_contacts</code> table automatically and is used afterwards to serve the "find as you type" auto-completion requests in a more efficient way.

	~~Moreover using a MySQL <code>FULLTEXT</code> index provides improved results when searching for "tokens". For instance an E-Mail address gets tokenized in the following way:~~

	~~<pre>~~
	~~jane.doe@somewhere.com~~
	~~yields the tokens~~: ~~"jane", "doe", "somewhere", and "com"~~
	~~</pre>~~

	Thus a user is able to start typing e.g. <code>"somewhere"</code> or <code>"doe"</code> to hit that search result. Without <code>FULLTEXT</code> index support, the user is supposed to enter <code>"jane"</code> to get that hit.

	~~== Configuring usage for MySQL <code>FULLTEXT</code> index ==~~

	~~For enabling the usage for a MySQL <code>FULLTEXT</code> index the property <code>"com.openexchange.contact.fulltextAutocomplete"<~~/~~code> needs to be set to "true" and a restart is supposed to be performed. Once set to <code>"true"<~~/~~code> an appropriate update task (<code>com~~.~~openexchange.contact.storage.rdb.groupware.AddFulltextIndexTask</code>) gets executed on next login attempts for associated database schemas.~~

	An administrator can even influence what fields are supposed to be considered for <code>FULLTEXT</code> -backed auto-complete executions by modifying <code>"com.openexchange.contact.fulltextIndexFields"</code> property.

	~~<b>Please Note</b>~~

	As explained above the update task gets only executed once (the time when a first login attempt for an associated database schema happens). In consequence, modifying the <code>"com.openexchange.contact.fulltextIndexFields"</code> property later on has no effect (even a restart does not get the changes applied). In order to apply the changes applied to <code>"com.openexchange.contact.fulltextIndexFields"</code> property, the associated update task is required being re-executed using the <code>"forceupdatetask"</code> command-line tool:

	~~<pre>~~
	~~/opt/~~open-xchange~~/sbin/forceupdatetask --task com.openexchange.contact.storage.rdb.groupware.AddFulltextIndexTask <other command-line arguments>~~
	~~</pre>~~

	~~Otherwise the <code>FULLTEXT</code> index will stop working.~~

	~~== MySQL configuration options ==~~

	~~An administrator may want to change how the MySQL <code>FULLTEXT</code> index works. MySQL only supports to change [[http://dev.mysql~~.com/~~doc/refman/5.~~7~~/en/fulltext-fine-tuning~~.~~html\|minimum/maximum word length]] and [[http://dev~~.~~mysql.com~~/~~doc~~/~~refman~~/~~5.7/en/fulltext-stopwords~~.html~~\|stop-words list]] for the InnoDB storage engine.~~

	The MySQL default value for the minimum word length is set to 3. In case users should be allowed to also retrieve results when typing less than 3 characters, the <code>"innodb_ft_min_token_size"</code> needs to be changed accordingly in the MySQL configuration file:

	~~<pre>~~
	~~innodb_ft_min_token_size=2~~
	~~</pre>~~

	~~<b>Please Note</b>~~

	~~Changing any of the MySQL configuration options requires that <code>FULLTEXT</code> indexes are re-created.~~

	~~Taken from MySQL reference manual:~~
	~~<pre>~~
	~~"...~~
	~~Rebuilding InnoDB Full-Text Indexes~~
	If you modify full-text variables that affect indexing (<code>innodb_ft_min_token_size, innodb_ft_max_token_size, innodb_ft_server_stopword_table, innodb_ft_user_stopword_table, innodb_ft_enable_stopword, ngram_token_size</code>) you must rebuild your <code>FULLTEXT</code> indexes after making the changes. Modifying the <code>innodb_ft_min_token_size, innodb_ft_max_token_size</code>, or <code>ngram_token_size</code> variables, which cannot be set dynamically, require restarting the server and rebuilding the indexes.

	~~To rebuild the <code>FULLTEXT</code> indexes for an InnoDB table, use <code>ALTER TABLE</code> with the <code>DROP INDEX</code> and <code>ADD INDEX</code> options to drop and re-create each index.~~
	~~..."~~
	~~</pre>~~

	Thus an administrator may manually drop and re-add the index or simply re-execute the associated update task (<code>com.openexchange.contact.storage.rdb.groupware.AddFulltextIndexTask</code>) as explained above using <code>"forceupdatetask"</code> command-line tool.

	~~[[Category: AppSuite]]~~
	~~[[Category: Administrator]]~~

Thorben at 13:29, 26 January 2016

2016-01-26T13:29:19Z

← Older revision		Revision as of 13:29, 26 January 2016
Line 4:		Line 4:

	__TOC__		__TOC__


			== Introduction ==

	With Open-Xchange Server v7.8.1 the MySQL <code>FULLTEXT</code> index is supported for retrieving auto-complete results. In order to use that capability an appropriate MySQL version needs to be in place. <code>FULLTEXT</code> indexes are supported starting with MySQL v5.6.4 (see http://dev.mysql.com/doc/refman/5.6/en/fulltext-restrictions.html for details).		With Open-Xchange Server v7.8.1 the MySQL <code>FULLTEXT</code> index is supported for retrieving auto-complete results. In order to use that capability an appropriate MySQL version needs to be in place. <code>FULLTEXT</code> indexes are supported starting with MySQL v5.6.4 (see http://dev.mysql.com/doc/refman/5.6/en/fulltext-restrictions.html for details).
Line 24:		Line 27:
	An administrator can even influence what fields are supposed to be considered for <code>FULLTEXT</code> -backed auto-complete executions by modifying <code>"com.openexchange.contact.fulltextIndexFields"</code> property.		An administrator can even influence what fields are supposed to be considered for <code>FULLTEXT</code> -backed auto-complete executions by modifying <code>"com.openexchange.contact.fulltextIndexFields"</code> property.

	~~===~~ Please Note ~~===~~		<b>Please Note</b>

	As explained above the update task gets only executed once (the time when a first login attempt for an associated database schema happens). In consequence, modifying the <code>"com.openexchange.contact.fulltextIndexFields"</code> property later on has no effect (even a restart does not get the changes applied). In order to apply the changes applied to <code>"com.openexchange.contact.fulltextIndexFields"</code> property, the associated update task is required being re-executed using the <code>"forceupdatetask"</code> command-line tool:		As explained above the update task gets only executed once (the time when a first login attempt for an associated database schema happens). In consequence, modifying the <code>"com.openexchange.contact.fulltextIndexFields"</code> property later on has no effect (even a restart does not get the changes applied). In order to apply the changes applied to <code>"com.openexchange.contact.fulltextIndexFields"</code> property, the associated update task is required being re-executed using the <code>"forceupdatetask"</code> command-line tool:
Line 44:		Line 47:
	</pre>		</pre>

	~~===~~ Please Note ~~===~~		<b>Please Note</b>

	Changing any of the MySQL configuration options requires that <code>FULLTEXT</code> indexes are re-created.		Changing any of the MySQL configuration options requires that <code>FULLTEXT</code> indexes are re-created.

	Taken from MySQL reference manual:		Taken from MySQL reference manual:
			<pre>
	"...		"...
	Rebuilding InnoDB Full-Text Indexes		Rebuilding InnoDB Full-Text Indexes
Line 55:		Line 59:
	To rebuild the <code>FULLTEXT</code> indexes for an InnoDB table, use <code>ALTER TABLE</code> with the <code>DROP INDEX</code> and <code>ADD INDEX</code> options to drop and re-create each index.		To rebuild the <code>FULLTEXT</code> indexes for an InnoDB table, use <code>ALTER TABLE</code> with the <code>DROP INDEX</code> and <code>ADD INDEX</code> options to drop and re-create each index.
	..."		..."
			</pre>

	Thus an administrator may manually drop and re-add the index or simply re-execute the associated update task (<code>com.openexchange.contact.storage.rdb.groupware.AddFulltextIndexTask</code>) as explained above using <code>"forceupdatetask"</code> command-line tool.		Thus an administrator may manually drop and re-add the index or simply re-execute the associated update task (<code>com.openexchange.contact.storage.rdb.groupware.AddFulltextIndexTask</code>) as explained above using <code>"forceupdatetask"</code> command-line tool.

Thorben at 13:26, 26 January 2016

2016-01-26T13:26:49Z

← Older revision		Revision as of 13:26, 26 January 2016
Line 50:		Line 50:
	Taken from MySQL reference manual:		Taken from MySQL reference manual:
	"...		"...
			Rebuilding InnoDB Full-Text Indexes
	== Rebuilding InnoDB Full-Text Indexes==
	If you modify full-text variables that affect indexing (<code>innodb_ft_min_token_size, innodb_ft_max_token_size, innodb_ft_server_stopword_table, innodb_ft_user_stopword_table, innodb_ft_enable_stopword, ngram_token_size</code>) you must rebuild your <code>FULLTEXT</code> indexes after making the changes. Modifying the <code>innodb_ft_min_token_size, innodb_ft_max_token_size</code>, or <code>ngram_token_size</code> variables, which cannot be set dynamically, require restarting the server and rebuilding the indexes.		If you modify full-text variables that affect indexing (<code>innodb_ft_min_token_size, innodb_ft_max_token_size, innodb_ft_server_stopword_table, innodb_ft_user_stopword_table, innodb_ft_enable_stopword, ngram_token_size</code>) you must rebuild your <code>FULLTEXT</code> indexes after making the changes. Modifying the <code>innodb_ft_min_token_size, innodb_ft_max_token_size</code>, or <code>ngram_token_size</code> variables, which cannot be set dynamically, require restarting the server and rebuilding the indexes.

Khgras: /* MySQL configuration options */

2016-01-20T13:36:54Z

MySQL configuration options

← Older revision		Revision as of 13:36, 20 January 2016
Line 36:		Line 36:
	== MySQL configuration options ==		== MySQL configuration options ==

	An administrator may want to change how the MySQL <code>FULLTEXT</code> index works. MySQL only supports to change minimum/maximum word length and stop-words list for the InnoDB storage engine.		An administrator may want to change how the MySQL <code>FULLTEXT</code> index works. MySQL only supports to change [[http://dev.mysql.com/doc/refman/5.7/en/fulltext-fine-tuning.html\|minimum/maximum word length]] and [[http://dev.mysql.com/doc/refman/5.7/en/fulltext-stopwords.html\|stop-words list]] for the InnoDB storage engine.

	The MySQL default value for the minimum word length is set to 3. In case users should be allowed to also retrieve results when typing less than 3 characters, the <code>"innodb_ft_min_token_size"</code> needs to be changed accordingly in the MySQL configuration file:		The MySQL default value for the minimum word length is set to 3. In case users should be allowed to also retrieve results when typing less than 3 characters, the <code>"innodb_ft_min_token_size"</code> needs to be changed accordingly in the MySQL configuration file:

Khgras: /* Note */

2016-01-20T13:34:32Z

Note

← Older revision		Revision as of 13:34, 20 January 2016
Line 44:		Line 44:
	</pre>		</pre>

	=== Note ===		=== Please Note ===

	Changing any of the MySQL configuration options requires that <code>FULLTEXT</code> indexes are re-created.		Changing any of the MySQL configuration options requires that <code>FULLTEXT</code> indexes are re-created.

Khgras at 13:34, 20 January 2016

2016-01-20T13:34:20Z

← Older revision		Revision as of 13:34, 20 January 2016
Line 1:		Line 1:
	<div class="title">Using MySQL FULLTEXT index for Auto-Complete</div>		<div class="title">Using MySQL <code>FULLTEXT</code> index for Auto-Complete</div>

	{{VersionFrom\|7.8.1}}		{{VersionFrom\|7.8.1}}
Line 5:		Line 5:
	__TOC__		__TOC__

	With Open-Xchange Server v7.8.1 the MySQL FULLTEXT index is supported for retrieving auto-complete results. In order to use that capability an appropriate MySQL version needs to be in place. FULLTEXT indexes are supported starting with MySQL v5.6.4 (see http://dev.mysql.com/doc/refman/5.6/en/fulltext-restrictions.html for details).		With Open-Xchange Server v7.8.1 the MySQL <code>FULLTEXT</code> index is supported for retrieving auto-complete results. In order to use that capability an appropriate MySQL version needs to be in place. <code>FULLTEXT</code> indexes are supported starting with MySQL v5.6.4 (see http://dev.mysql.com/doc/refman/5.6/en/fulltext-restrictions.html for details).

	Once enabled, an appropriate index is created on the 'prg_contacts' table automatically and is used afterwards to serve the "find as you type" auto-completion requests in a more efficient way.		Once enabled, an appropriate index is created on the <code>prg_contacts</code> table automatically and is used afterwards to serve the "find as you type" auto-completion requests in a more efficient way.

	Moreover using a MySQL FULLTEXT index provides improved results when searching for "tokens". For instance an E-Mail address gets tokenized in the following way:		Moreover using a MySQL <code>FULLTEXT</code> index provides improved results when searching for "tokens". For instance an E-Mail address gets tokenized in the following way:

	<pre>		<pre>
Line 16:		Line 16:
	</pre>		</pre>

	Thus a user is able to start typing e.g. "somewhere" or "doe" to hit that search result. Without FULLTEXT index support, the user is supposed to enter "jane" to get that hit.		Thus a user is able to start typing e.g. <code>"somewhere"</code> or <code>"doe"</code> to hit that search result. Without <code>FULLTEXT</code> index support, the user is supposed to enter <code>"jane"</code> to get that hit.

	== Configuring usage for MySQL FULLTEXT index ==		== Configuring usage for MySQL <code>FULLTEXT</code> index ==

	For enabling the usage for a MySQL FULLTEXT index the property "com.openexchange.contact.fulltextAutocomplete" needs to be set to "true" and a restart is supposed to be performed. Once set to "true" an appropriate update task (com.openexchange.contact.storage.rdb.groupware.AddFulltextIndexTask) gets executed on next login attempts for associated database schemas.		For enabling the usage for a MySQL <code>FULLTEXT</code> index the property <code>"com.openexchange.contact.fulltextAutocomplete"</code> needs to be set to "true" and a restart is supposed to be performed. Once set to <code>"true"</code> an appropriate update task (<code>com.openexchange.contact.storage.rdb.groupware.AddFulltextIndexTask</code>) gets executed on next login attempts for associated database schemas.

	An administrator can even influence what fields are supposed to be considered for FULLTEXT -backed auto-complete executions by modifying "com.openexchange.contact.fulltextIndexFields" property.		An administrator can even influence what fields are supposed to be considered for <code>FULLTEXT</code> -backed auto-complete executions by modifying <code>"com.openexchange.contact.fulltextIndexFields"</code> property.

	''Note''		=== Please Note ===
	As explained above the update task gets only executed once (the time when a first login attempt for an associated database schema happens). In consequence, modifying the "com.openexchange.contact.fulltextIndexFields" property later on has no effect (even a restart does not get the changes applied). In order to apply the changes applied to "com.openexchange.contact.fulltextIndexFields" property, the associated update task is required being re-executed using the "forceupdatetask" command-line tool:
			As explained above the update task gets only executed once (the time when a first login attempt for an associated database schema happens). In consequence, modifying the <code>"com.openexchange.contact.fulltextIndexFields"</code> property later on has no effect (even a restart does not get the changes applied). In order to apply the changes applied to <code>"com.openexchange.contact.fulltextIndexFields"</code> property, the associated update task is required being re-executed using the <code>"forceupdatetask"</code> command-line tool:

	<pre>		<pre>
Line 31:		Line 32:
	</pre>		</pre>

	Otherwise the FULLTEXT index will stop working.		Otherwise the <code>FULLTEXT</code> index will stop working.

	== MySQL configuration options ==		== MySQL configuration options ==

	An administrator may want to change how the MySQL FULLTEXT index works. MySQL only supports to change minimum/maximum word length and stop-words list for the InnoDB storage engine.		An administrator may want to change how the MySQL <code>FULLTEXT</code> index works. MySQL only supports to change minimum/maximum word length and stop-words list for the InnoDB storage engine.

			The MySQL default value for the minimum word length is set to 3. In case users should be allowed to also retrieve results when typing less than 3 characters, the <code>"innodb_ft_min_token_size"</code> needs to be changed accordingly in the MySQL configuration file:

	The MySQL default value for the minimum word length is set to 3. In case users should be allowed to also retrieve results when typing less than 3 characters, the "innodb_ft_min_token_size" needs to be changed accordingly in the MySQL configuration file:
	<pre>		<pre>
	innodb_ft_min_token_size=2		innodb_ft_min_token_size=2
	</pre>		</pre>

	''Note''		=== Note ===
	Changing any of the MySQL configuration options requires that FULLTEXT indexes are re-created.
			Changing any of the MySQL configuration options requires that <code>FULLTEXT</code> indexes are re-created.

	Taken from MySQL reference manual:		Taken from MySQL reference manual:
	"...		"...

	== Rebuilding InnoDB Full-Text Indexes==		== Rebuilding InnoDB Full-Text Indexes==
	If you modify full-text variables that affect indexing (innodb_ft_min_token_size, innodb_ft_max_token_size, innodb_ft_server_stopword_table, innodb_ft_user_stopword_table, innodb_ft_enable_stopword, ngram_token_size) you must rebuild your FULLTEXT indexes after making the changes. Modifying the innodb_ft_min_token_size, innodb_ft_max_token_size, or ngram_token_size variables, which cannot be set dynamically, require restarting the server and rebuilding the indexes.		If you modify full-text variables that affect indexing (<code>innodb_ft_min_token_size, innodb_ft_max_token_size, innodb_ft_server_stopword_table, innodb_ft_user_stopword_table, innodb_ft_enable_stopword, ngram_token_size</code>) you must rebuild your <code>FULLTEXT</code> indexes after making the changes. Modifying the <code>innodb_ft_min_token_size, innodb_ft_max_token_size</code>, or <code>ngram_token_size</code> variables, which cannot be set dynamically, require restarting the server and rebuilding the indexes.

	To rebuild the FULLTEXT indexes for an InnoDB table, use ALTER TABLE with the DROP INDEX and ADD INDEX options to drop and re-create each index.		To rebuild the <code>FULLTEXT</code> indexes for an InnoDB table, use <code>ALTER TABLE</code> with the <code>DROP INDEX</code> and <code>ADD INDEX</code> options to drop and re-create each index.
	..."		..."

	Thus an administrator may manually drop and re-add the index or simply re-execute the associated update task (com.openexchange.contact.storage.rdb.groupware.AddFulltextIndexTask) as explained above using "forceupdatetask" command-line tool.		Thus an administrator may manually drop and re-add the index or simply re-execute the associated update task (<code>com.openexchange.contact.storage.rdb.groupware.AddFulltextIndexTask</code>) as explained above using <code>"forceupdatetask"</code> command-line tool.

	[[Category: AppSuite]]		[[Category: AppSuite]]
	[[Category: Administrator]]		[[Category: Administrator]]

Khgras at 13:23, 20 January 2016

2016-01-20T13:23:11Z

← Older revision		Revision as of 13:23, 20 January 2016
Line 1:		Line 1:
	<div class="title">~~Cross~~-~~context database~~</div>		<div class="title">Using MySQL FULLTEXT index for Auto-Complete</div>

	{{VersionFrom\|7.8.0}}		{{VersionFrom\|7.8.1}}

	__TOC__		__TOC__

	~~= Using MySQL FULLTEXT index for Auto-Complete =~~

	With Open-Xchange Server v7.8.1 the MySQL FULLTEXT index is supported for retrieving auto-complete results. In order to use that capability an appropriate MySQL version needs to be in place. FULLTEXT indexes are supported starting with MySQL v5.6.4 (see http://dev.mysql.com/doc/refman/5.6/en/fulltext-restrictions.html for details).		With Open-Xchange Server v7.8.1 the MySQL FULLTEXT index is supported for retrieving auto-complete results. In order to use that capability an appropriate MySQL version needs to be in place. FULLTEXT indexes are supported starting with MySQL v5.6.4 (see http://dev.mysql.com/doc/refman/5.6/en/fulltext-restrictions.html for details).

Khgras at 13:22, 20 January 2016

2016-01-20T13:22:35Z

← Older revision		Revision as of 13:22, 20 January 2016
Line 56:		Line 56:

	Thus an administrator may manually drop and re-add the index or simply re-execute the associated update task (com.openexchange.contact.storage.rdb.groupware.AddFulltextIndexTask) as explained above using "forceupdatetask" command-line tool.		Thus an administrator may manually drop and re-add the index or simply re-execute the associated update task (com.openexchange.contact.storage.rdb.groupware.AddFulltextIndexTask) as explained above using "forceupdatetask" command-line tool.

			[[Category: AppSuite]]
			[[Category: Administrator]]

Khgras at 13:21, 20 January 2016

2016-01-20T13:21:59Z

← Older revision		Revision as of 13:21, 20 January 2016
Line 5:		Line 5:
	__TOC__		__TOC__

	Using MySQL FULLTEXT index for Auto-Complete		= Using MySQL FULLTEXT index for Auto-Complete =

	With Open-Xchange Server v7.8.1 the MySQL FULLTEXT index is supported for retrieving auto-complete results. In order to use that capability an appropriate MySQL version needs to be in place. FULLTEXT indexes are supported starting with MySQL v5.6.4 (see http://dev.mysql.com/doc/refman/5.6/en/fulltext-restrictions.html for details).		With Open-Xchange Server v7.8.1 the MySQL FULLTEXT index is supported for retrieving auto-complete results. In order to use that capability an appropriate MySQL version needs to be in place. FULLTEXT indexes are supported starting with MySQL v5.6.4 (see http://dev.mysql.com/doc/refman/5.6/en/fulltext-restrictions.html for details).

	Once enabled, an appropriate index is created on the 'prg_contacts' table automatically and is used afterwards to serve the "find as you type" auto-completion requests in a more efficient way.		Once enabled, an appropriate index is created on the 'prg_contacts' table automatically and is used afterwards to serve the "find as you type" auto-completion requests in a more efficient way.

	Moreover using a MySQL FULLTEXT index provides improved results when searching for "tokens". For instance an E-Mail address gets tokenized in the following way:		Moreover using a MySQL FULLTEXT index provides improved results when searching for "tokens". For instance an E-Mail address gets tokenized in the following way:

			<pre>
	jane.doe@somewhere.com		jane.doe@somewhere.com
	yields the tokens: "jane", "doe", "somewhere", and "com"		yields the tokens: "jane", "doe", "somewhere", and "com"
			</pre>

	Thus a user is able to start typing e.g. "somewhere" or "doe" to hit that search result. Without FULLTEXT index support, the user is supposed to enter "jane" to get that hit.		Thus a user is able to start typing e.g. "somewhere" or "doe" to hit that search result. Without FULLTEXT index support, the user is supposed to enter "jane" to get that hit.
	Configuring usage for MySQL FULLTEXT index
			== Configuring usage for MySQL FULLTEXT index ==

	For enabling the usage for a MySQL FULLTEXT index the property "com.openexchange.contact.fulltextAutocomplete" needs to be set to "true" and a restart is supposed to be performed. Once set to "true" an appropriate update task (com.openexchange.contact.storage.rdb.groupware.AddFulltextIndexTask) gets executed on next login attempts for associated database schemas.		For enabling the usage for a MySQL FULLTEXT index the property "com.openexchange.contact.fulltextAutocomplete" needs to be set to "true" and a restart is supposed to be performed. Once set to "true" an appropriate update task (com.openexchange.contact.storage.rdb.groupware.AddFulltextIndexTask) gets executed on next login attempts for associated database schemas.

	An administrator can even influence what fields are supposed to be considered for FULLTEXT -backed auto-complete executions by modifying "com.openexchange.contact.fulltextIndexFields" property.		An administrator can even influence what fields are supposed to be considered for FULLTEXT -backed auto-complete executions by modifying "com.openexchange.contact.fulltextIndexFields" property.
	Note
			''Note''
	As explained above the update task gets only executed once (the time when a first login attempt for an associated database schema happens). In consequence, modifying the "com.openexchange.contact.fulltextIndexFields" property later on has no effect (even a restart does not get the changes applied). In order to apply the changes applied to "com.openexchange.contact.fulltextIndexFields" property, the associated update task is required being re-executed using the "forceupdatetask" command-line tool:		As explained above the update task gets only executed once (the time when a first login attempt for an associated database schema happens). In consequence, modifying the "com.openexchange.contact.fulltextIndexFields" property later on has no effect (even a restart does not get the changes applied). In order to apply the changes applied to "com.openexchange.contact.fulltextIndexFields" property, the associated update task is required being re-executed using the "forceupdatetask" command-line tool:

			<pre>
	/opt/open-xchange/sbin/forceupdatetask --task com.openexchange.contact.storage.rdb.groupware.AddFulltextIndexTask <other command-line arguments>		/opt/open-xchange/sbin/forceupdatetask --task com.openexchange.contact.storage.rdb.groupware.AddFulltextIndexTask <other command-line arguments>
			</pre>

	Otherwise the FULLTEXT index will stop working.		Otherwise the FULLTEXT index will stop working.
	MySQL configuration options
			== MySQL configuration options ==

	An administrator may want to change how the MySQL FULLTEXT index works. MySQL only supports to change minimum/maximum word length and stop-words list for the InnoDB storage engine.		An administrator may want to change how the MySQL FULLTEXT index works. MySQL only supports to change minimum/maximum word length and stop-words list for the InnoDB storage engine.

	The MySQL default value for the minimum word length is set to 3. In case users should be allowed to also retrieve results when typing less than 3 characters, the "innodb_ft_min_token_size" needs to be changed accordingly in the MySQL configuration file:		The MySQL default value for the minimum word length is set to 3. In case users should be allowed to also retrieve results when typing less than 3 characters, the "innodb_ft_min_token_size" needs to be changed accordingly in the MySQL configuration file:
			<pre>
	innodb_ft_min_token_size=2		innodb_ft_min_token_size=2
	Note		</pre>

			''Note''
	Changing any of the MySQL configuration options requires that FULLTEXT indexes are re-created.		Changing any of the MySQL configuration options requires that FULLTEXT indexes are re-created.
	Taken from MySQL reference manual:		Taken from MySQL reference manual:
	"...		"...
	Rebuilding InnoDB Full-Text Indexes
			== Rebuilding InnoDB Full-Text Indexes==
	If you modify full-text variables that affect indexing (innodb_ft_min_token_size, innodb_ft_max_token_size, innodb_ft_server_stopword_table, innodb_ft_user_stopword_table, innodb_ft_enable_stopword, ngram_token_size) you must rebuild your FULLTEXT indexes after making the changes. Modifying the innodb_ft_min_token_size, innodb_ft_max_token_size, or ngram_token_size variables, which cannot be set dynamically, require restarting the server and rebuilding the indexes.		If you modify full-text variables that affect indexing (innodb_ft_min_token_size, innodb_ft_max_token_size, innodb_ft_server_stopword_table, innodb_ft_user_stopword_table, innodb_ft_enable_stopword, ngram_token_size) you must rebuild your FULLTEXT indexes after making the changes. Modifying the innodb_ft_min_token_size, innodb_ft_max_token_size, or ngram_token_size variables, which cannot be set dynamically, require restarting the server and rebuilding the indexes.

	To rebuild the FULLTEXT indexes for an InnoDB table, use ALTER TABLE with the DROP INDEX and ADD INDEX options to drop and re-create each index.		To rebuild the FULLTEXT indexes for an InnoDB table, use ALTER TABLE with the DROP INDEX and ADD INDEX options to drop and re-create each index.
	..."		..."

	Thus an administrator may manually drop and re-add the index or simply re-execute the associated update task (com.openexchange.contact.storage.rdb.groupware.AddFulltextIndexTask) as explained above using "forceupdatetask" command-line tool.		Thus an administrator may manually drop and re-add the index or simply re-execute the associated update task (com.openexchange.contact.storage.rdb.groupware.AddFulltextIndexTask) as explained above using "forceupdatetask" command-line tool.

Khgras: Created page with "
Cross-context database
{{VersionFrom|7.8.0}} TOC Using MySQL FULLTEXT index for Auto-Complete With Open-Xchange Server v7.8.1 the MySQL FULLTEX..."

2016-01-20T13:17:47Z

Created page with "<div class="title">Cross-context database</div> {{VersionFrom|7.8.0}} __TOC__ Using MySQL FULLTEXT index for Auto-Complete With Open-Xchange Server v7.8.1 the MySQL FULLTEX..."

New page

<div class="title">Cross-context database</div>

{{VersionFrom|7.8.0}}

__TOC__

Using MySQL FULLTEXT index for Auto-Complete
With Open-Xchange Server v7.8.1 the MySQL FULLTEXT index is supported for retrieving auto-complete results. In order to use that capability an appropriate MySQL version needs to be in place. FULLTEXT indexes are supported starting with MySQL v5.6.4 (see http://dev.mysql.com/doc/refman/5.6/en/fulltext-restrictions.html for details).
Once enabled, an appropriate index is created on the 'prg_contacts' table automatically and is used afterwards to serve the "find as you type" auto-completion requests in a more efficient way.
Moreover using a MySQL FULLTEXT index provides improved results when searching for "tokens". For instance an E-Mail address gets tokenized in the following way:
jane.doe@somewhere.com
yields the tokens: "jane", "doe", "somewhere", and "com"
Thus a user is able to start typing e.g. "somewhere" or "doe" to hit that search result. Without FULLTEXT index support, the user is supposed to enter "jane" to get that hit.
Configuring usage for MySQL FULLTEXT index
For enabling the usage for a MySQL FULLTEXT index the property "com.openexchange.contact.fulltextAutocomplete" needs to be set to "true" and a restart is supposed to be performed. Once set to "true" an appropriate update task (com.openexchange.contact.storage.rdb.groupware.AddFulltextIndexTask) gets executed on next login attempts for associated database schemas.
An administrator can even influence what fields are supposed to be considered for FULLTEXT -backed auto-complete executions by modifying "com.openexchange.contact.fulltextIndexFields" property.
Note
As explained above the update task gets only executed once (the time when a first login attempt for an associated database schema happens). In consequence, modifying the "com.openexchange.contact.fulltextIndexFields" property later on has no effect (even a restart does not get the changes applied). In order to apply the changes applied to "com.openexchange.contact.fulltextIndexFields" property, the associated update task is required being re-executed using the "forceupdatetask" command-line tool:
/opt/open-xchange/sbin/forceupdatetask --task com.openexchange.contact.storage.rdb.groupware.AddFulltextIndexTask <other command-line arguments>
Otherwise the FULLTEXT index will stop working.
MySQL configuration options
An administrator may want to change how the MySQL FULLTEXT index works. MySQL only supports to change minimum/maximum word length and stop-words list for the InnoDB storage engine.
The MySQL default value for the minimum word length is set to 3. In case users should be allowed to also retrieve results when typing less than 3 characters, the "innodb_ft_min_token_size" needs to be changed accordingly in the MySQL configuration file:
innodb_ft_min_token_size=2
Note
Changing any of the MySQL configuration options requires that FULLTEXT indexes are re-created.
Taken from MySQL reference manual:
"...
Rebuilding InnoDB Full-Text Indexes
If you modify full-text variables that affect indexing (innodb_ft_min_token_size, innodb_ft_max_token_size, innodb_ft_server_stopword_table, innodb_ft_user_stopword_table, innodb_ft_enable_stopword, ngram_token_size) you must rebuild your FULLTEXT indexes after making the changes. Modifying the innodb_ft_min_token_size, innodb_ft_max_token_size, or ngram_token_size variables, which cannot be set dynamically, require restarting the server and rebuilding the indexes.
To rebuild the FULLTEXT indexes for an InnoDB table, use ALTER TABLE with the DROP INDEX and ADD INDEX options to drop and re-create each index.
..."
Thus an administrator may manually drop and re-add the index or simply re-execute the associated update task (com.openexchange.contact.storage.rdb.groupware.AddFulltextIndexTask) as explained above using "forceupdatetask" command-line tool.