Supporting of search-as-you-type using sql in databases

    17 Votes

A search as you type system computes answer on the fly as user types in keyword query character by character. It finds the solution for both multi-keyword queries and single keyword queries. It also Finds how to support search as you type in relational database using SQL. During Exact search, a user types a single partial key word W character by character,search as you type system finds the record that contain keyword with a prefix W. It is also known as prefix search.

Fuzzy search: As user types in a single partial keyword "W" character by character ,system finds records with keywords similar to the query keyword. For example when we types in query "corel" ,record r7  is relevant answer  since it contain keyword "correlation". Edit distance measure is used for finding similarity between strings. Exact search gives a multi-keyword query Q with m keywords w1,w2,…wm. Wm is considered as partial keyword and others are complete keyword. Fuzzy search finds the record that contains  keyword similar to the complete keyword  and a keyword with a prefix similar to the partial keyword W. Wm is the partial keyword which used the edit distance.

Different approaches for search as you type
  • Use separate application layer - It can achieve high performance                               
  • Use database extenders - Not safe method to query engine. Depends on API of specific database
  • Use SQL - More compatible since it is using standard SQL. More portable to different platforms than other method
Exact search for single keyword

There are two methods adopted for Exact search for single keyword. They are No index and Index based methods.

No index methods - Issues a SQL query that scans each records and verifies whether the record is an answer to the query. It calls user defined functions (UDF) and ddd functions to the database. Also uses LIKE predicate.

Index based method - This method builds auxiliary tables as index structures, Inverted index table and Prefix table. Find the similar prefixes from the prefix table(PT). Then it finds the keyword ranges of similar prefixes. Then it finds the answer based on the keyword ranges using inverted index table. To find similar prefixes - UDF, Gram-based method and Neighbourhood generation method is used.

Steps for exact search

  • For given partial keyword W
  • Get the keyword range [lkid,ukid] using prefix table
  • Find the records that have a keyword in the range through the inverted index table IT
  • Then use the SQL query to answer the prefix-search query W.
Download this file (Supporting of search-as-you-type using sql in databases.ppt)Supporting of search-as-you-type using sql in databases.ppt[PPT Presentation]205 Kb