Note: This feature is available with the Spanner Enterprise edition and Enterprise Plus edition. For more information, see theSpanner editions overview.

This page describes what full-text search is and how it works.

A full-text search lets you build an application that can search a table to findwords, phrases, or numbers, instead of just searching for exact matches instructured fields. Full-text searches return the latesttransactionally-consistent committed data to your application. Spannerfull-text search capabilities also include making spelling corrections,automating language detection of search input, and ranking search results.Spanner automatically expands text searches to includeapproximate word matching.

You need to create a search index on any columns that you want to make availablefor full-text searches. Spanner analyzes thedata in those columns to identify individual words to add to the search index.Spanner updates the search index with new or modified data as soon asit's committed in the database.

Types of full-text search

• Basic text search: Searches for content using the entire or part of aword or phrase to reliably receive results. Example query predicates:
  • Matching all words [tricolor rat terrier]
  • Exact word or phrases ["rat terrier"]
  • Any of these words [miniature OR standard]
  • Word within close proximity [world AROUND(3) cup]
  • Substring [start*]
• Numeric search: Performs numeric equality and inequality searches.Equality searches match a number. Range and inequality searches matcha number within a specific range.
• n-gram-based search: Matches words with spelling variations, includingproper nouns and names. This type of search also helps to match query textwith misspelled names, names with alternate spellings, and text with otherspelling variations.
• Soundex searches: Matches similar-sounding words.

Full-text search features

Spanner full-text search has the following features:

• Ranked search results: Computes a score to gauge how well a querymatches a document (for example, giving a heavier weight for column_A). UseSQL expressions to customize ranking.
• Snippets: Highlights the matching text in the search result.
• Global support: Automatically supports tokenization in differentlanguages, includingCJKsegmentation. Manual specification of language lets you perform additionalfine-tuning.
• Governance: Finds every occurrence of specific words.
• Spelling correction: Automatically corrects misspelled words in queriesto match the correctly-spelled word in storage. For example, if theuser searches for "girafe", the search finds documents with "giraffe".
• Contextual synonym addition, including stop words: Automatically addscontextually-relevant synonyms to increase recall. For example, "the house"matches "this house" and "cat picture" matches "kitty picture".
• Contextual number translation to and from text: Matches the textualversion of a number to the numeric representation and vice-versa. Forexample, "five cats" matches "5 cats".
• Automatic plural conversion: Matches "cat" to "cats".

Full-text search concepts

Full-text search has the following key concepts:

• Adocument refers to the searchable data in a given row.
• Atoken refers to each word of a document that's stored in a search index.
• Atokenization process splits a document into tokens.
• Atokenizer is a SQL function used for tokenization.
• Aninverted index stores tokens. Use SQL queries to search theinverted index.

Use case example for full-text search

To understand full-text search, let's take a look at an application thatuses a database to store songs for each singer. Each row is a single song. Eachsong contains columns like title, lyrics, singer, and album. The applicationuses full-text search to let a user search for a song using natural languagequeries:

• The search supports queries that use theOR operator, likePrince ORCamille. Applications can directly feed the end user input from the searchbox into the SQLSEARCHfunction (using the rquery syntax). For more information, seeQuery asearchindex.
• Spanner uses search indexes to look for matching documentsacross different fields. For example, an application can issue a query tosearch for "cry" in the title, with "so cold" in the lyrics, and "Prince" asthe singer.

Other uses for search indexes

Search indexes have a variety of uses in addition to full-text search, such asthe following:

• Indexing elements in array columns. Consider an application that uses anarray column to store tags associated with an item. With search indexes, theapplication can efficiently look up rows containing a specific tag. For moreinformation, seeArray tokenization.

• Finding data that resides in the intersection of a setof query conditions. For example, you can use an arbitrary set of attributes(color, size, brand, rating, and so on) to search for a product in acatalog.

• Using numeric search conditions, alone or in combination with full-textconditions. Some examples for when a search index is useful for numericsearches:

  • When it's combined with a full-text application. For example, to find anemail with the subjectPicture and size greater than 1 MB.
  • When it's part of an intersection of conditions described previously.For example, to find products wherecolor = "yellow" AND size = 14 ANDrating >= 4.5.
  • When searching for the intersection of numeric columns. Forexample, consider a table storing event start and end times. Searchindexes can efficiently implement a query that looks for events thattook place at a particular point in time:start_time <= @p ANDend_time > @p.

  For more information, seeNumeric indexes.

Full-text search steps

In Spanner, full-text search requires the following steps:

1. Tokenize a document using the Spanner tokenizer functions,such asTOKENIZE_SUBSTRING.For more information, seeTokenization.
2. Create a search index to hold the tokens using theCREATE SEARCHINDEXDDL statement. For more information, seeSearchindexes.
3. Query documents in the search index using the SpannerSEARCHfunction. For more information, seeQueryoverview.
4. Rank the results of the query using the SpannerSCOREfunction. For more information, seeRank searchresults.

Limitations

• Full-text search doesn't supportAssured Workloads.

Pricing

There are no additional charges from Spanner when you usefull-text search, although the implementation of full-text searchincreases costs due to the need for additional compute and storage resources.

For more information, seeSpanner pricing.

What's next

• Learn abouttokenization and Spanner tokenizers.
• Learn aboutsearch indexes.
• Learn aboutfull-text search queries.