Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
  • Indexed full text search in PHP-MySQL (PART - 2)

    • 0
    • 3
    • 3
    • 1
    • 0
    • 0
    • 0
    • 0
    • 347
    Comment on it

    We already discussed Natural full text search in Indexed full text search in PHP-MySQL (PART - 1).
    Lets look into Boolean Full Text Search.

    In this we can use various operators to change the behavior of search. To specify a search to be boolean, we need to use the IN BOOLEAN MODE modifier. This mode can also work without FULL TEXT INDEX, however, the search operation will be very slow. Common words(also known as stopwords) are ignored.

    In boolean search, search string is interpreted using the rules of a special query language. MySQL implements boolean search by using implied boolean logic in which the following operators are allowed and interpreted as :

    1. + : acts as boolean AND. This indicates that this word must be present in each row of the resultset.

    2. - : acts as boolean NOT. This indicates that this word must not be present in any row of the result set.

    3. [no operator] : means boolean OR. When neither + nor - is present, the word is optional but if present in any any row, the row is rated higher. This is identical to the behavior of full text search without boolean mode.

    4. > OR < : These operators changes the relevance of row. > increases the relevancy and < decreases it.

    5. () : Parentheses group words into subexpressions. Parenthesized groups can be nested.

    6. ~ : Works like negation operator and decreases the relevancy of the row containing the word.
      Although the relevancy is decreased but the row will not be removed from the result set.

    7. * : It is a wildcard operator but instead of preceding to the word, it needs to be appended to the word. If a word is specified with truncation operator as well as appended with *, it is not striped out from query, no matter how small the word is.

    8. : It is used for literal match of a phrase. Full text search engine performs a FULL TEXT index search by splitting the phrase into words. It is important to remember that the characters that enclose the phrase cannot be treated as quotation marks enclosing the search string itself, they are operator characters that delimit the phrase.

    Some example search strings with boolean full-text operators :

    'Software Development'
    Find rows that contains at least one the above words.

    '+Software +Development'
    Find rows that contains both words.

    '+Software Development'
    Find rows that contain the word Software, but rank rows higher if they also contain Development.

    '+Software -Development'
    Find rows that contain the word Software but not Development.

    '+Software ~Development'
    Find rows that contain the word Software, but if the row also contains the word Development, rate it lower than if row does not. This is softer than a search for '+ Software -Development', for which the presence of Development causes the row not to be returned at all.

    '+Software +(>Development <Maintenance)'
    Find rows that contain the words Software and Development, or Software and Maintenance (in any order), but rank Software Development higher than Software Maintenance.

    Find rows that contain words such as Develop, Developer, Development, or Developed.

    'A phrase'
    Find rows that contain the exact phrase A phrase (for example, rows that contain like A phrase but not A good phrase).

 0 Comment(s)

Sign In

Sign up using

Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: