Full-Text Search

Velocity provides full-text search capabilities across all supported database systems, enabling efficient text searching with relevance scoring.

Overview

Full-text search (FTS) allows you to search large text fields for keywords and phrases with ranking by relevance. Velocity normalizes search scores across all databases to a 0-100 scale, where higher scores indicate better matches.

Results include a SCORE column (case-insensitive) of type double, accessed via row.GetFieldDouble("score").

Creating Full-Text Indexes

Full-text indexes are created on tables using the CreateFullTextIndex() method:

var schema = new Schema("myschema");
var table = schema.CreateTable("documents");
table.CreateColumn("id", DataType.Integer);
table.CreatePrimaryKey("pk_documents", "id");
table.CreateColumn("title", DataType.Varchar, size: 200);
table.CreateColumn("content", DataType.Clob);

// Create full-text index on title and content columns
var ftsIndex = table.CreateFullTextIndex(
    name: "idx_fts_documents",
    columnNames: ["title", "content"]
);

Querying with Full-Text Search

Use Manager.FullTextSearch() for streaming results or Manager.FullTextRetrieval() for in-memory results:

using (var manager = new Manager(connection))
{
    var schema = manager.LoadSchema("myschema");
    var documentsTable = schema["documents"];

    // Specify which columns to return in results
    var columns = [
        documentsTable["id"],
        documentsTable["title"],
        documentsTable["content"]
    ];

    // FullTextSearch returns a ResultReader (streaming, low memory)
    using (ResultReader reader = manager.FullTextSearch(documentsTable, columns, "quantum computing"))
    {
        foreach (Result row in reader)
        {
            double score = row.GetFieldDouble("score"); // Normalized 0-100
            string title = row.GetFieldVarChar("title");
            Console.WriteLine($"[{score:F2}] {title}");
        }
    }
}

For smaller result sets, use FullTextRetrieval() which loads all results into memory:

using (var manager = new Manager(connection))
{
    var schema = manager.LoadSchema("myschema");
    var documentsTable = schema["documents"];
    var columns = [ documentsTable["id"], documentsTable["title"] ];

    // FullTextRetrieval returns a ResultSet (all results in memory)
    ResultSet results = manager.FullTextRetrieval(documentsTable, columns, "quantum computing");

    Console.WriteLine($"Found {results.Count} documents");

    foreach (Result row in results)
    {
        double score = row.GetFieldDouble("score"); // Normalized 0-100
        string title = row.GetFieldVarChar("title");
        Console.WriteLine($"[{score:F2}] {title}");
    }
}

Search Scores

Velocity normalizes full-text search scores to a consistent 0-100 range across all database systems. The score is returned as a double column named SCORE in every result row:

double score = row.GetFieldDouble("score");

Score ranges:

100: Perfect or near-perfect match
75-99: Strong relevance
50-74: Moderate relevance
25-49: Weak relevance
0-24: Minimal relevance

The normalized score allows you to build consistent relevance thresholds regardless of the underlying database.

Database-Specific Search Syntax

Each database system has its own full-text search query syntax. Velocity passes your search query directly to the underlying database engine, so you must use the syntax appropriate for your target database.

DB2

DB2 uses the CONTAINS predicate with its Text Search query syntax.

Example:

// Boolean AND
manager.FullTextSearch(table, columns, "quantum AND computing")

// Boolean OR
manager.FullTextSearch(table, columns, "quantum OR photon")

// Phrase search
manager.FullTextSearch(table, columns, "\"quantum computing\"")

// Proximity
manager.FullTextSearch(table, columns, "quantum NEAR/5 computing")

Documentation: DB2 Text Search

MySQL

MySQL uses MATCH AGAINST in NATURAL LANGUAGE MODE. Pass plain space-separated words — no boolean operators, no wildcards. MySQL scores results by natural language relevance.

Example:

// Natural language search — words ranked by relevance
manager.FullTextSearch(table, columns, "quantum computing")

// Multiple terms — MySQL matches documents containing any of the words
manager.FullTextSearch(table, columns, "quantum photon laser")

Note

Boolean Mode operators (+term, -term, *) are not supported — Velocity always uses NATURAL LANGUAGE MODE.

Documentation: MySQL Full-Text Search

Oracle

Oracle uses CONTAINS with Oracle Text query syntax.

Example:

// Boolean AND (requires explicit AND)
manager.FullTextSearch(table, columns, "quantum AND computing")

// Boolean OR
manager.FullTextSearch(table, columns, "quantum OR photon")

// Phrase search
manager.FullTextSearch(table, columns, "{quantum computing}")

// NEAR operator
manager.FullTextSearch(table, columns, "NEAR((quantum, computing), 10)")

// Wildcard
manager.FullTextSearch(table, columns, "comput%")

Documentation: Oracle Text Query Syntax

PostgreSQL

PostgreSQL uses plainto_tsquery, which converts plain text into a tsquery using AND logic between terms. It does not accept raw tsquery syntax — operators like &, |, and <-> are treated as plain text, not operators.

Example:

// Plain text — all terms ANDed together automatically
manager.FullTextSearch(table, columns, "quantum computing")

// Multiple terms — all must appear in the document
manager.FullTextSearch(table, columns, "machine learning algorithms")

Note

tsquery operators (&, |, !, <->) are not supported — Velocity uses plainto_tsquery which handles plain natural language input.

Documentation: PostgreSQL Text Search

SQL Server

SQL Server uses FREETEXTTABLE, which performs linguistic analysis (stemming, inflectional forms) on plain text. Pass natural language words — boolean operators and proximity syntax are not supported.

Example:

// Natural language search — SQL Server stems and inflects terms automatically
manager.FullTextSearch(table, columns, "quantum computing")

// Multiple terms — FREETEXTTABLE matches inflected forms (e.g. "run" matches "running", "ran")
manager.FullTextSearch(table, columns, "machine learning algorithms")

Note

CONTAINS-style syntax (boolean AND/OR, NEAR((x,y),5), wildcards) is not supported — Velocity uses FREETEXTTABLE for linguistic analysis and relevance ranking.

Documentation: SQL Server Full-Text Search

SQLite

SQLite uses FTS5 with boolean operators, phrase queries, and prefix search.

Example:

// Boolean AND
manager.FullTextSearch(table, columns, "quantum AND computing")

// Boolean OR
manager.FullTextSearch(table, columns, "quantum OR photon")

// Phrase search
manager.FullTextSearch(table, columns, "\"quantum computing\"")

// Prefix search
manager.FullTextSearch(table, columns, "comput*")

Documentation: SQLite FTS5

Teradata

Teradata uses a custom BM25-based full-text search engine deployed as a set of UDFs and stored procedures in the fts_metadata schema. The extension must be installed separately before use — see Teradata Full-Text Search for deployment instructions.

Full-text search on Teradata requires that FullTextSearchEnabled is set to true on the TeradataDatasourceConnection. When it is not enabled, FTS operations are silently skipped.

The search query is a plain string of words — no boolean operators, no wildcards. All terms are matched independently (OR semantics) and BM25 ranks results by relevance. Common words such as the and a are not filtered.

Example:

// Simple word search — OR semantics, ranked by BM25 relevance
manager.FullTextSearch(table, columns, "quantum computing")

// Multi-word search — documents matching more terms score higher
manager.FullTextSearch(table, columns, "wireless bluetooth headphones")

Note

Teradata FTS does not support boolean operators (AND, OR, NOT), phrase search, or wildcards. Pass plain space-separated words only. See Teradata Full-Text Search for full query syntax documentation.

Documentation: Teradata Full-Text Search

Multi-Column Searches

Full-text indexes can span multiple columns. Search relevance considers matches across all indexed columns:

var articlesTable = schema.CreateTable("articles");
articlesTable.CreateColumn("id", DataType.Integer);
articlesTable.CreatePrimaryKey("pk_articles", "id");
articlesTable.CreateColumn("title", DataType.Varchar, size: 200);
articlesTable.CreateColumn("abstract", DataType.Varchar, size: 500);
articlesTable.CreateColumn("body", DataType.Clob);

// Create full-text index spanning multiple columns
var ftsIndex = articlesTable.CreateFullTextIndex(
    name: "idx_fts_articles",
    columnNames: ["title", "abstract", "body"]
);

// Build the schema
manager.BuildSchema(schema);

// Search across all indexed columns
var columns = [articlesTable["id"], articlesTable["title"], articlesTable["abstract"]];
var results = manager.FullTextRetrieval(articlesTable, columns, "machine learning");

Performance Considerations

Index Size: Full-text indexes can be large, especially for CLOB/TEXT columns
Update Overhead: Index maintenance occurs during INSERT/UPDATE operations
Query Planning: Most databases optimize FTS queries automatically
Language Support: Some databases require language configuration for stemming and stop words

Limitations

Not all column types support full-text indexing (typically limited to CHAR, VARCHAR, TEXT, CLOB)
Search syntax is not portable across databases - you must use database-specific query syntax
Some databases require additional configuration (e.g., Oracle Text installation)
Score normalization provides consistency but may not reflect database-specific relevance algorithms

Best Practices

Index Selectively: Only index columns you'll actually search
Use Streaming for Large Results: Use FullTextSearch() for large result sets to avoid loading everything into memory

Set Score Thresholds: Filter results by minimum score to exclude low-relevance results:

using (ResultReader reader = manager.FullTextSearch(table, columns, query))
{
    foreach (Result row in reader)
    {
        if (row.GetFieldDouble("score") >= 50.0) // Only matches with score >= 50
        {
            // Process result
        }
    }
}

Use Appropriate Syntax: Consult your database's documentation for optimal query syntax
Test Search Queries: Full-text behavior varies significantly between databases
Consider Language: Configure appropriate language analyzers for your content

Example: Complete Search Implementation

using System;
using System.Collections.Generic;
using YndigoBlue.Velocity;
using YndigoBlue.Velocity.Engine;
using YndigoBlue.Velocity.Interfaces;
using YndigoBlue.Velocity.Model;

public class DocumentSearch
{
    private readonly IDatasourceConnection _connection;
    private readonly string _schemaName;

    public DocumentSearch(IDatasourceConnection connection, string schemaName)
    {
        _connection = connection;
        _schemaName = schemaName;
    }

    public List<SearchResult> Search(string searchTerm, double minScore = 25.0)
    {
        using (var manager = new Manager(_connection))
        {
            var schema = manager.LoadSchema(_schemaName);
            var documentsTable = schema["documents"];

            // Define columns to retrieve
            var columns = [
                documentsTable["id"],
                documentsTable["title"],
                documentsTable["author"],
                documentsTable["published_date"],
                documentsTable["content"]
            ];

            var results = new List<SearchResult>();

            // Use FullTextSearch for streaming (low memory)
            using (ResultReader reader = manager.FullTextSearch(documentsTable, columns, searchTerm))
            {
                foreach (Result row in reader)
                {
                    double score = row.GetFieldDouble("score");

                    // Only include results with meaningful relevance
                    if (score >= minScore)
                    {
                        results.Add(new SearchResult
                        {
                            Id = row.GetFieldInteger("id"),
                            Title = row.GetFieldVarChar("title"),
                            Author = row.GetFieldVarChar("author"),
                            PublishedDate = row.GetFieldDateTime("published_date"),
                            Snippet = GetSnippet(row.GetFieldVarChar("content"), 200),
                            RelevanceScore = score
                        });
                    }
                }
            }

            return results;
        }
    }

    private string GetSnippet(string content, int maxLength)
    {
        if (string.IsNullOrEmpty(content) || content.Length <= maxLength)
            return content;

        return content.Substring(0, maxLength) + "...";
    }
}

public class SearchResult
{
    public int Id { get; set; }
    public string Title { get; set; }
    public string Author { get; set; }
    public DateTime? PublishedDate { get; set; }
    public string Snippet { get; set; }
    public double RelevanceScore { get; set; }
}

Table of Contents

Full-Text Search

Overview

Creating Full-Text Indexes

Querying with Full-Text Search

Search Scores

Database-Specific Search Syntax

DB2

MySQL

Note

Oracle

PostgreSQL

Note

SQL Server

Note

SQLite

Teradata

Note

Multi-Column Searches

Performance Considerations

Limitations

Best Practices

Example: Complete Search Implementation

See Also