Description\n-----------\n\nHybrid search combines the keyword precision of full-text search with the conceptual understanding of vector search to produce a single, superior set of results.\n\nFull-Text Search\n\nFull-text search is the traditional keyword-based search, excelling at finding documents that contain the exact words from your query. Behind the scenes, it relies on a data structure called an inverted index—a dictionary that maps each word to a list of documents it appears in, allowing for very fast lookups. For instance, a search for 'apple pie recipe' will instantly find all documents indexed under those three words: ‘apple‘, ‘pie‘ & ‘recipe‘.\n\nVector Search\n\nVector search is a modern search method based on meaning. It finds documents that are conceptually similar to your query, even if they don't share any keywords. It works by converting both the query and the documents into numerical representations called "vector embeddings." These vectors exist as points in a high-dimensional conceptual space. A search then finds the "nearest neighbors"—the document points that are closest to the query point. For instance, a vector search for "apple pie recipe" might also return a document titled "how to bake a Granny Smith tart," because the model understands that "tart" is similar to "pie" and "Granny Smith" is a type of "apple."\n\nThe Power of Hybrid Search\n\nFull-text search offers precise keyword matching, while vector search provides nuanced understanding of concepts. Together, they handle ambiguity and ensure critical queries aren't missed, resulting in a robust, intelligent search experience.\n\nCombining Full-Text and Vector Search\n\nA Search for "Sustainable Coffee Pods"\n\nWhen a user searches for "sustainable coffee pods," the two search systems return the following ranked lists.\n\nFull-Text Search Results\n\n| Rank | ID | Title                         |\n| ---- | -- | ----------------------------- |\n| 1    | 1  | 'Eco Coffee Pods - 100 Count' |\n| 2    | 3  | 'Recyclable Coffee Capsules'  |\n| 3    | 4  | 'Morning Roast Coffee Beans'  |\n\nVector Search Results\n\n| Rank | ID | Title                           |\n| ---- | -- | ------------------------------- |\n| 1    | 2  | 'Compostable Espresso Pods'     |\n| 2    | 3  | 'Recyclable Coffee Capsules'    |\n| 3    | 6  | 'Bamboo Reusable Coffee Filter' |\n\nWhen two search systems produce distinct lists of titles, a traditional merging approach would prioritize titles that appear in both lists, similar to an INNER JOIN operation.\n\nWhy a Simple INNER JOIN Is Insufficient\n\nAn INNER JOIN only returns results found by both search systems, discarding valuable items that appear in only one list.\n\nIf we were to INNER JOIN the two lists from our "sustainable coffee pods" search, the result would be:\n\n| ID | Title                        |\n| -- | ---------------------------- |\n| 3  | 'Recyclable Coffee Capsules' |\n\nThis result is severely incomplete. It correctly finds the one common item but completely discards the #1 ranked result from both searches ('Eco Coffee Pods' and 'Compostable Espresso Pods') simply because they were specialists found by only one system.\n\nReciprocal Rank Fusion (RRF)\n\nTo solve the problem of discarded results, we use Reciprocal Rank Fusion (RRF). The power of RRF lies in its simplicity: it operates on the rank of an item (1st, 2nd, 3rd) in a list, not its raw, non-comparable score. This makes it highly effective for merging lists from different systems without needing to normalize their scores.\n\nA helpful way to think of this is to imagine your different search systems as a "panel of expert advisors"; RRF intelligently combines their opinions using the formula:\n\n$$\nRRF_{score} = \\sum_{i} \\frac{1}{k + \\text{rank}_i}\n$$\n\nWhere rank is the position of a document in a list, and k is a tuning constant that moderates the influence of that rank.\n\nRRF's k Parameter\n\nThe k parameter is the primary tuning knob for the RRF algorithm, acting as a smoothing factor that controls how results are weighted. A low k gives immense power to a top-ranked result, while a high k is more skeptical of a single top pick and rewards items found by multiple systems (consensus). A value of k=60 is a robust and effective baseline for general use.\n\nA key advantage of RRF is that it ignores the raw scores from the search systems and uses only the rank. The table below shows the partial RRF score for the top items in our "sustainable coffee pods" search, calculated with k=60.\n\nBuilding the Hybrid Query Optimization model with CTEs\n\n``mermaid\n\ngraph TD\n    subgraph "1. Configuration & Parameters"\n        param_k[/"Parameter: k (e.g., 60)"/]\n    end\n    subgraph "2. Initial Searches (CTEs)"\n        A[CTE: vector_limit_search] -->|Outputs 'dist' score| C\n        B[CTE: fulltext_limit_search] -->|Outputs 'match_score'| D\n    end\n    subgraph "3. Score Calculation & Merging (CTEs)"\n        C[CTE: vector_score]\n        D[CTE: fulltext_score]\n        param_k --> C & D\n        C -->|"Calculates partial_rrf"| E[CTE: full_outer_join_output <br> Implemented with UNION]\n        D -->|"Calculates partial_rrf"| E\n    end\n    subgraph "4. Final Output"\n        E --> F[Final SELECT & ORDER BY total_rrf]\n    end\n    style F fill:#f5ddb3\n`\n\nTo build the model, our query uses the following Common Table Expressions (CTEs). \n\n| CTE Name                  | Purpose                                                                                    |\n| ------------------------- | ------------------------------------------------------------------------------------------ |\n| vector_limit_search     | Runs the initial vector search to retrieve the top N conceptually similar items.           |\n| fulltext_limit_search   | Runs the initial full-text search to retrieve the top N keyword matches.                   |\n| vector_score             | Calculates a partial_rrf score for each item from the vector search based on its rank.    |\n| fulltext_score           | Calculates a partial_rrf score for each item from the full-text search based on its rank. |\n| full_outer_join_output | Merges the two score lists and sums the partial scores into a total_rrf.                  |\n\nThe annotated SQL query uses the CTEs to perform the hybrid search:\n\n`sql\n-- Set the parameters for our query.\nSET @k = 60; -- The RRF smoothing constant.\nSET @search_term = "sustainable coffee pods";\n-- The vector for the search term would be set here.\n-- SET @search_term_vector = VEC_FromText("...");\n\n-- Use CTEs to break the logic into sequential steps.\nWITH\n-- STEP 1: Get top results from each search method.\nvector_limit_search AS (\n  SELECT id, title, VEC_DISTANCE_EUCLIDEAN(embedding, @search_term_vector) AS dist\n  FROM products ORDER BY dist ASC LIMIT 10\n),\nfulltext_limit_search AS (\n  SELECT id, title, MATCH(title) AGAINST (@search_term) AS match_score\n  FROM products WHERE MATCH(title) AGAINST (@search_term)\n  ORDER BY match_score DESC LIMIT 10\n),\n-- STEP 2: Calculate partial RRF scores for each list.\nvector_score AS (\n  SELECT id, title, 1 / (@k + RANK() OVER (ORDER BY dist ASC)) AS partial_rrf\n  FROM vector_limit_search\n),\nfulltext_score AS (\n  SELECT id, title, 1 / (@k + RANK() OVER (ORDER BY match_score DESC)) AS partial_rrf\n  FROM fulltext_limit_search\n),\n-- STEP 3: Merge the two lists and sum the scores.\nfull_outer_join_output AS (\n  SELECT v.id, v.title, (v.partial_rrf + IFNULL(f.partial_rrf, 0)) AS total_rrf\n  FROM vector_score v LEFT JOIN fulltext_score f USING (id)\n  UNION\n  SELECT f.id, f.title, (IFNULL(v.partial_rrf, 0) + f.partial_rrf) AS total_rrf\n  FROM fulltext_score f LEFT JOIN vector_score v USING (id)\n)\n-- STEP 4: Select the final, unified list.\nSELECT id, title, total_rrf FROM full_outer_join_output\nORDER BY total_rrf DESC\nLIMIT 10;\n``\n\nFinal Calculation for "Sustainable Coffee Pods"\n\nWith k=60, the final CTE merges and sums the partial scores. The consensus item, found in both lists, rises to the top.\n\n| ID | Title                      | fulltext_rrf | vector_rrf | total_rrf | Final Rank |\n| -- | -------------------------- | ------------- | ----------- | ---------- | ---------- |\n| 3  | Recyclable Coffee Capsules | 0.01613       | 0.01613     | 0.03226    | 1          |\n| 1  | Eco Coffee Pods            | 0.01639       | 0           | 0.01639    | 2          |\n| 2  | Compostable Espresso Pods  | 0             | 0.01639     | 0.01639    | 2          |\n\nTuning the k Parameter\n\nAs introduced earlier, the k parameter can be fine-tuned for specific situations. Here are three common scenarios to consider.\n\n1. Combining Diverse, Specialist Systems\n\nFor merging results from different methods like keyword and vector search, a higher k (such as 60) is ideal to balance their contributions. Our main "sustainable coffee pods" search is a perfect illustration of this, where the consensus item wins but the high-quality specialists are ranked immediately after.\n\n| Final Rank | ID | Title                      | Total RRF | Note             |\n| ---------- | -- | -------------------------- | --------- | ---------------- |\n| 1          | 3  | Recyclable Coffee Capsules | 0.03226   | Consensus Winner |\n| 2          | 1  | Eco Coffee Pods            | 0.01639   | Specialist       |\n| 2          | 2  | Compostable Espresso Pods  | 0.01639   | Specialist       |\n\n2. Handling Mixed-Quality or "Noisy" Systems\n\nIf you are fusing results from reliable systems and one experimental, less predictable system, a higher k (60 or more) is the safest choice to prevent an outlier from disproportionately influencing the final rank.\n\nConsider our search with a third, "noisy" system that incorrectly ranks 'Eco-Friendly Car Wax' (ID 8) at #1. A high k value minimizes the impact of this error.\n\n<table><thead><tr><th>Product</th><th>FT Rank</th><th width="129.44140625">Vector Rank</th><th width="122.640625">Noisy Rank</th><th>Total RRF (k=60)</th></tr></thead><tbody><tr><td>Recyclable Coffee Capsules</td><td>2</td><td>2</td><td>-</td><td>1/62 + 1/62 = 0.03226</td></tr><tr><td>Eco-Friendly Car Wax</td><td>-</td><td>-</td><td>1</td><td>1/61 = 0.01639</td></tr></tbody></table>\n\nThe high k value correctly ensures that the consensus result from the two reliable systems easily beats the single, erroneous result from the noisy system.\n\n3. Fusing High-Quality, Similar Systems\n\nIf you are combining lists from two very similar, high-performing algorithms, you can experiment with a slightly lower k (30-50) to give more weight to a top-ranked document.\n\nConsider a case where we fuse two similar vector models (Vector_A, Vector_B) that both rank 'Compostable Espresso Pods' as #1 and 'Recyclable Coffee Capsules' as #2. A lower k makes the winner more decisive.\n\n| Product             | Ranks       | Total RRF (k=30)     | Total RRF (k=60)     |\n| ------------------- | ----------- | -------------------- | -------------------- |\n| Compostable Pods    | 1, 1        | 1/31 + 1/31 = 0.0645 | 1/61 + 1/61 = 0.0328 |\n| Recyclable Capsules | 2, 2        | 1/32 + 1/32 = 0.0625 | 1/62 + 1/62 = 0.0322 |\n| Score Difference    | <p><br></p> | 0.0020               | 0.0006               |\n\nWith k=30, the score separation between the #1 and #2 results is more than three times larger, showing higher confidence in the top result, which is desirable when you trust both systems.\n\nDetermining the Best k (The Experimental Method)\n\nA formal, 3-step process can be used to scientifically determine the best k value for your data through offline evaluation. To illustrate this process, which requires a pre-judged "ground truth" set with graded relevance, we will use a separate, self-contained case study.\n\n1. Gather Prerequisites\n\nYou need: Multiple Ranked Lists, a "Ground Truth" Set, and an Evaluation Metric (like NDCG). With a query like "healthy breakfast", your ground truth might look like this:\n\n| Doc ID | Title                  | Relevance  |\n| ------ | ---------------------- | ---------- |\n| A      | 'Oatmeal with Berries' | 3 (High)   |\n| B      | 'Green Smoothie'       | 3 (High)   |\n| C      | 'Avocado Toast'        | 2 (Medium) |\n| D      | 'Bacon and Eggs'       | 1 (Low)    |\n| E      | 'Cinnamon Roll'        | 0 (None)   |\n\nAnd your raw ranked lists might be:\n\n Full-Text Results: \\[D, A, E]\n Vector Results: \\[B, C, A]\n\n2. Run the Experiment\n\nYou iterate through k values, applying the RRF formula to the raw lists to generate a final ranking for each k.\n\n Final Ranked List (k=10):\n\n| Rank | ID | Title                  |\n| ---- | -- | ---------------------- |\n| 1    | B  | 'Green Smoothie'       |\n| 2    | D  | 'Bacon and Eggs'       |\n| 3    | A  | 'Oatmeal with Berries' |\n| 4    | C  | 'Avocado Toast'        |\n| 5    | E  | 'Cinnamon Roll'        |\n\n Final Ranked List (k=60):\n\n| Rank | ID | Title                  |\n| ---- | -- | ---------------------- |\n| 1    | A  | 'Oatmeal with Berries' |\n| 2    | B  | 'Green Smoothie'       |\n| 3    | C  | 'Avocado Toast'        |\n| 4    | D  | 'Bacon and Eggs'       |\n| 5    | E  | 'Cinnamon Roll'        |\n\n3. Analyze and Select\n\nYou use your metric to "grade" each list against the Ground Truth and choose the k with the highest score. The k=60 list is clearly better as it placed the two "Highly Relevant" documents (A and B) at the top.\n\n| k Value | Performance Score (NDCG) |\n| ------- | ------------------------ |\n| 10      | 0.85                     |\n| 60      | 0.92                     |\n\nIn this experiment, k=60 is the winner. A key advantage of RRF is that its performance is "not critically sensitive to the choice of k, making it a robust and reliable method" for improving search relevance.\n\nFurther Reading\n\n* Reciprocal Rank Fusion for IR (SIGIR '09): The original research paper that proposed the RRF method.\n\nURL: https://mariadb.com/docs/server/reference/sql-structure/vectors/optimizing-hybrid-search-query-with-reciprocal-rank-fusion-rrf