MySQL Full-Text Search (Enterprise)
Users of the Enterprise version of dbFront 1.1.3 have direct access to the Full-Text search functionality found in MySQL, Oracle and SQL Server.
MySql Full-Text searches can be run in five different modes: Default, Like (L:), Boolean (B:), Natural Language (N:) and Query Expansion (E:).
To use Full-Text search in MySQL, specialized FULLTEXT indexes must be created. See Creating Full Text Indexes below for instructions.
Boolean Full-Text Search
As soon as dbFront sees any boolean search operators then it will run the search request in MySQL's Boolean Mode. You can also force dbFront to use Boolean mode by preceding the search string with "B:".
In boolean mode you can prefix a word with '+' to require that word, or '-' to reject rows containing that word. Any operators designed to affect sort order are ignored.
- B: : Starting a search with 'B:' will tell dbFront to use MySQL's Boolean Mode.
- +: The word must be included. e.g. '+John +Smith', both "John" and "Smith" must be found in the data.
- -: The word must be excluded. e.g. '-Fred +Smith', skip all rows that contain the word "Fred" but require "Smith".
- (, ): Group expression. e.g. '+mysql +(tutorial training)', return all rows containing "MySQL" and either "tutorial" or "training".
- prefix*: Return all words that start with the specified prefix, e.g. '+John* +Smith', return all rows that contain "Smith" and variations of "John" such as "John", "Johnny" or "Johnson".
Natural Language Full-Text Search
If the search text is prefixed with "N:", then dbFront will submit the query in MySQL's Natural Language Mode.
The Natural Language Mode is not as magical as the name might imply. MySQL simply queries for all of the words as entered. Natural Language Mode does better as the data set increases.
Enclose your search string in quotes if you need to look for a quote or exact string. e.g 'N:"Fred is Red" '.
Query Expansion Full-Text Search
If the search text is prefixed with "E:", then dbFront will submit the query in MySQL's Query Expansion Mode.
Query Expansion Mode causes MySQL to run the Natural Language Full-Text Search twice. The results of the first query are used to expand the keywords used for the second query. This can create more noise but it often includes valuable results based on keywords the user did not initially think to specify.
Like Natural Language Mode, Query Expansion Mode returns better results as the size of the dataset increases.
Creating Full-Text Indexes
MySQL supports adding a FULLTEXT index on CHAR, VARCHAR and TEXT columns in both MyISAM and InnoDB table types. Adding a FULLTEXT index in MySQL is straightforward and can be done via CREATE TABLE, ALTER TABLE or CREATE INDEX.
WARNING: Don't create a single MySql Full-Text index with multiple columns. Adding a single full-text index containing multiple columns prevents dbFront from using that index to search a single column.
Adding a full-text index on a table is as simple as running:
ALTER TABLE table_name ADD FULLTEXT(column_name);