Skip to main content
Version: V1.0.0

Experience hybrid search

This tutorial will guide you through the hybrid search feature of seekdb, demonstrating how it combines the strengths of full-text keyword search and vector-based semantic search to provide more accurate and comprehensive search results.

Overview​

Hybrid search combines vector-based semantic search and full-text keyword search to deliver more accurate and comprehensive search results. Vector search excels at semantic similarity matching but may struggle with precise keyword, numeric, and proper noun matches. Full-text search effectively compensates for this limitation. seekdb provides hybrid search functionality through the DBMS_HYBRID_SEARCH system package, supporting the following scenarios:

  • Pure vector search: Find content based on semantic similarity, suitable for semantic search and recommendation systems.
  • Pure full-text search: Find content based on keyword matches, suitable for document and product searches.
  • Hybrid search: Combine keyword matching and semantic understanding to provide more precise and comprehensive search results.

This feature is widely used in intelligent search, document search, and product recommendation scenarios.

Prerequisites​

  • Obtain the database connection string from your administrator and execute the following command to connect to the database: 
    - host: The IP address for connecting to the seekdb database.
    - port: The port for connecting to the seekdb database.
    - database_name: The name of the database to access.
    - user_name: The database username.
    - password: The database password.
    mysql -h$host -P$port -u$user_name -p$password -D$database_name
  • A test table has been created, and vector and full-text indexes have been established within the table: 
    CREATE TABLE doc_table(
        c1 INT,
        vector VECTOR(3),
        query VARCHAR(255),
        content VARCHAR(255),
        VECTOR INDEX idx1(vector) WITH (distance=l2, type=hnsw, lib=vsag),
        FULLTEXT INDEX idx2(query),
        FULLTEXT INDEX idx3(content)
    );

    INSERT INTO doc_table VALUES
    (1, '[1,2,3]', "hello world", "oceanbase Elasticsearch database"),
    (2, '[1,2,1]', "hello world, what is your name", "oceanbase mysql database"),
    (3, '[1,1,1]', "hello world, how are you", "oceanbase oracle database"),
    (4, '[1,3,1]', "real world, where are you from", "postgres oracle database"),
    (5, '[1,3,2]', "real world, how old are you", "redis oracle database"),
    (6, '[2,1,1]', "hello world, where are you from", "starrocks oceanbase database");

Vector search finds content based on semantic similarity by calculating vector similarity, suitable for semantic search and recommendation systems.

Set the search parameters and perform a vector search to find records most similar to the query vector [1,2,3]:

SET @parm = '{
    "knn" : {
        "field": "vector", 
        "k": 3, 
        "query_vector": [1,2,3] 
    }
}';

SELECT JSON_PRETTY(DBMS_HYBRID_SEARCH.SEARCH('doc_table', @parm));

The returned results are as follows:

+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| JSON_PRETTY(DBMS_HYBRID_SEARCH.SEARCH('doc_table', @parm))                                                                                                                                                                                                                                                                                                                                                                                                                       |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| [
  {
    "c1": 1,
    "query": "hello world",
    "_score": 1.0,
    "vector": "[1,2,3]",
    "content": "oceanbase Elasticsearch database"
  },
  {
    "c1": 5,
    "query": "real world, how old are you",
    "_score": 0.41421356,
    "vector": "[1,3,2]",
    "content": "redis oracle database"
  },
  {
    "c1": 2,
    "query": "hello world, what is your name",
    "_score": 0.33333333,
    "vector": "[1,2,1]",
    "content": "oceanbase mysql database"
  }
] |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

The results are sorted by vector similarity, with _score indicating the similarity score. A higher score means greater similarity.

Step 2: Pure full-text search​

Full-text search finds content based on keyword matches, suitable for document and product searches.

Set the search parameters and perform a full-text search to find records containing the keywords in the query and content fields:

SET @parm = '{
    "query": {
        "query_string": {
            "fields": ["query", "content"],
            "query": "hello oceanbase"
        }
    }
}';

SELECT JSON_PRETTY(DBMS_HYBRID_SEARCH.SEARCH('doc_table', @parm));

The returned results are as follows:

+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| JSON_PRETTY(DBMS_HYBRID_SEARCH.SEARCH('doc_table', @parm))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| [
  {
    "c1": 1,
    "query": "hello world",
    "_score": 0.37162162162162166,
    "vector": "[1,2,3]",
    "content": "oceanbase Elasticsearch database"
  },
  {
    "c1": 2,
    "query": "hello world, what is your name",
    "_score": 0.3503184713375797,
    "vector": "[1,2,1]",
    "content": "oceanbase mysql database"
  },
  {
    "c1": 3,
    "query": "hello world, how are you",
    "_score": 0.3503184713375797,
    "vector": "[1,1,1]",
    "content": "oceanbase oracle database"
  },
  {
    "c1": 6,
    "query": "hello world, where are you from",
    "_score": 0.3503184713375797,
    "vector": "[2,1,1]",
    "content": "starrocks oceanbase database"
  }
] |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

The results are sorted by keyword match score, with _score indicating the match score. A higher score means better matching.

Hybrid search combines keyword matching and semantic understanding to provide more precise and comprehensive search results, leveraging the strengths of both full-text and vector indexes.

Set the search parameters and perform both full-text and vector searches:

SET @parm = '{
    "query": {
        "query_string": {
            "fields": ["query", "content"],
            "query": "hello oceanbase"
        }
    },
    "knn" : {
        "field": "vector",
        "k": 5,
        "query_vector": [1,2,3]
    }
}';

SELECT json_pretty(DBMS_HYBRID_SEARCH.SEARCH('doc_table', @parm));

The returned results are as follows:

+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| JSON_PRETTY(DBMS_HYBRID_SEARCH.SEARCH('doc_table', @parm))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| [
  {
    "c1": 1,
    "query": "hello world",
    "_score": 0.37162162162162166,
    "vector": "[1,2,3]",
    "content": "oceanbase Elasticsearch database"
  },
  {
    "c1": 2,
    "query": "hello world, what is your name",
    "_score": 0.3503184713375797,
    "vector": "[1,2,1]",
    "content": "oceanbase mysql database"
  },
  {
    "c1": 3,
    "query": "hello world, how are you",
    "_score": 0.3503184713375797,
    "vector": "[1,1,1]",
    "content": "oceanbase oracle database"
  },
  {
    "c1": 6,
    "query": "hello world, where are you from",
    "_score": 0.3503184713375797,
    "vector": "[2,1,1]",
    "content": "starrocks oceanbase database"
  }
] |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MySQL [test]> SET @parm = '{
    '>     "query": {
    '>         "query_string": {
    '>             "fields": ["query", "content"],
    '>             "query": "hello oceanbase"
    '>         }
    '>     },
    '>     "knn" : {
    '>         "field": "vector",
    '>         "k": 5,
    '>         "query_vector": [1,2,3]
    '>     }
    '> }';
Query OK, 0 rows affected (0.00 sec)

MySQL [test]>
MySQL [test]> SELECT json_pretty(DBMS_HYBRID_SEARCH.SEARCH('doc_table', @parm));
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| json_pretty(DBMS_HYBRID_SEARCH.SEARCH('doc_table', @parm))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| [
  {
    "c1": 1,
    "query": "hello world",
    "_score": 1.3716216216216217,
    "vector": "[1,2,3]",
    "content": "oceanbase Elasticsearch database"
  },
  {
    "c1": 2,
    "query": "hello world, what is your name",
    "_score": 0.6836518013375796,
    "vector": "[1,2,1]",
    "content": "oceanbase mysql database"
  },
  {
    "c1": 3,
    "query": "hello world, how are you",
    "_score": 0.6593354613375797,
    "vector": "[1,1,1]",
    "content": "oceanbase oracle database"
  },
  {
    "c1": 5,
    "query": "real world, how old are you",
    "_score": 0.41421356,
    "vector": "[1,3,2]",
    "content": "redis oracle database"
  },
  {
    "c1": 6,
    "query": "hello world, where are you from",
    "_score": 0.3503184713375797,
    "vector": "[2,1,1]",
    "content": "starrocks oceanbase database"
  },
  {
    "c1": 4,
    "query": "real world, where are you from",
    "_score": 0.30901699,
    "vector": "[1,3,1]",
    "content": "postgres oracle database"
  }
] |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

Hybrid search results consider both keyword match scores (_keyword_score) and semantic similarity scores (_semantic_score). The final _score is the sum of these two scores, used to comprehensively sort the search results.

Parameter tuning​

In hybrid search, you can adjust the weights of full-text and vector searches using the boost parameter to optimize search results. For example, to increase the weight of full-text search:

SET @parm = '{
    "query": {
        "query_string": {
            "fields": ["query", "content"],
            "query": "hello oceanbase",
            "boost": 2.0
        }
    },
    "knn" : {
        "field": "vector",
        "k": 5,
        "query_vector": [1,2,3],
        "boost": 1.0
    }
}';

SELECT json_pretty(DBMS_HYBRID_SEARCH.SEARCH('doc_table', @parm));

By adjusting the boost parameter, you can control the weight of keyword search and semantic search in the final sorting. For example, if you prioritize keyword matching, you can increase the boost value for query_string; if you prioritize semantic similarity, you can increase the boost value for knn.

Summary​

Through this tutorial, you have mastered the core features of seekdb's hybrid search:

  • Pure vector search: Find relevant content based on semantic similarity, suitable for semantic search scenarios.
  • Pure full-text search: Find content based on keyword matches, suitable for precise search scenarios.
  • Hybrid search: Combine keywords and semantic understanding to provide more comprehensive and accurate search results.

Hybrid search is an ideal choice for handling large volumes of unstructured data and building intelligent search and recommendation systems, significantly improving the accuracy and comprehensiveness of search results.

Next steps​

More operations​

For more information on experiencing the AI Native features of seekdb and building AI applications based on seekdb, see:

In addition to using SQL, you can also operate using the Python SDK (pyseekdb) provided by seekdb. For more information, see Experience embedded usage with Python SDK and pyseekdb overview.

Contents

Edit this page