|
|
| 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]]
| |