Building Fast Video Search with SQLite FTS5

#sqlite #search #php #tutorial

Full-text search is essential for any content platform. While Elasticsearch and Meilisearch are popular choices, SQLite's built-in FTS5 extension provides surprisingly capable search for small to medium datasets. Here's how I implemented it for DailyWatch.

Creating the FTS5 Table

FTS5 uses "virtual tables" that maintain a separate search index:

-- Content table (your regular table)
CREATE TABLE videos (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    video_id TEXT UNIQUE NOT NULL,
    title TEXT NOT NULL,
    description TEXT,
    channel_title TEXT,
    category_id INTEGER,
    thumbnail_url TEXT,
    view_count INTEGER DEFAULT 0,
    published_at TEXT,
    fetched_at TEXT DEFAULT (datetime('now'))
);

-- FTS5 virtual table linked to content table
CREATE VIRTUAL TABLE videos_fts USING fts5(
    title,
    description,
    channel_title,
    content='videos',
    content_rowid='id',
    tokenize='porter unicode61 remove_diacritics 2'
);

Key options:

content='videos' links to the main table
content_rowid='id' maps FTS rows to the main table's primary key
tokenize='porter unicode61' enables stemming and Unicode support

Keeping the Index in Sync

Since we're using a content-linked FTS table, we need triggers to keep it synchronized:

-- Trigger for INSERT
CREATE TRIGGER videos_ai AFTER INSERT ON videos BEGIN
    INSERT INTO videos_fts(rowid, title, description, channel_title)
    VALUES (new.id, new.title, new.description, new.channel_title);
END;

-- Trigger for DELETE
CREATE TRIGGER videos_ad AFTER DELETE ON videos BEGIN
    INSERT INTO videos_fts(videos_fts, rowid, title, description, channel_title)
    VALUES ('delete', old.id, old.title, old.description, old.channel_title);
END;

-- Trigger for UPDATE
CREATE TRIGGER videos_au AFTER UPDATE ON videos BEGIN
    INSERT INTO videos_fts(videos_fts, rowid, title, description, channel_title)
    VALUES ('delete', old.id, old.title, old.description, old.channel_title);
    INSERT INTO videos_fts(rowid, title, description, channel_title)
    VALUES (new.id, new.title, new.description, new.channel_title);
END;

Search Implementation in PHP

class VideoSearch {
    private const CACHE_TTL = 21600; // 6 hours

    public function __construct(
        private readonly PDO $db,
        private readonly string $cachePath = 'data/cache/search/',
    ) {}

    public function search(string $query, int $limit = 20, int $offset = 0): array {
        $query = trim($query);
        if (strlen($query) < 2) return [];

        // Check cache
        $cacheKey = md5($query . $limit . $offset);
        $cached = $this->getCache($cacheKey);
        if ($cached !== null) return $cached;

        // Sanitize for FTS5 syntax
        $ftsQuery = $this->buildFtsQuery($query);

        $stmt = $this->db->prepare('
            SELECT v.video_id, v.title, v.channel_title, v.thumbnail_url,
                   v.view_count, v.duration, v.published_at,
                   rank
            FROM videos_fts
            JOIN videos v ON v.id = videos_fts.rowid
            WHERE videos_fts MATCH ?
            ORDER BY rank
            LIMIT ? OFFSET ?
        ');

        $stmt->execute([$ftsQuery, $limit, $offset]);
        $results = $stmt->fetchAll(PDO::FETCH_ASSOC);

        $this->setCache($cacheKey, $results);

        return $results;
    }

    private function buildFtsQuery(string $input): string {
        // Remove FTS5 special characters to prevent syntax errors
        $cleaned = preg_replace('/["*(){}\[\]^~\\]/', '', $input);

        // Split into words and wrap each in quotes for exact matching
        $words = preg_split('/\s+/', $cleaned, -1, PREG_SPLIT_NO_EMPTY);
        $words = array_filter($words, fn($w) => mb_strlen($w) >= 2);

        if (empty($words)) return '""';

        // Use implicit AND: all terms must match
        return implode(' ', array_map(fn($w) => '"' . $w . '"', $words));
    }

    private function getCache(string $key): ?array {
        $file = $this->cachePath . $key . '.json';
        if (file_exists($file) && (time() - filemtime($file)) < self::CACHE_TTL) {
            return json_decode(file_get_contents($file), true);
        }
        return null;
    }

    private function setCache(string $key, array $data): void {
        if (!is_dir($this->cachePath)) mkdir($this->cachePath, 0755, true);
        file_put_contents($this->cachePath . $key . '.json', json_encode($data));
    }
}

Usage in a Controller

$search = new VideoSearch($db);
$query = $_GET['q'] ?? '';
$results = $search->search($query, limit: 20);

// Render results
foreach ($results as $video) {
    echo "<div class='video-card'>";
    echo "  <img src='{$video['thumbnail_url']}' alt='' loading='lazy'>";
    echo "  <h3>{$video['title']}</h3>";
    echo "  <p>{$video['channel_title']}</p>";
    echo "</div>";
}

Performance

On dailywatch.video with ~15,000 indexed videos:

Simple single-word search: 1-2ms
Multi-word search: 2-4ms
With result caching: <1ms for repeated queries

FTS5 is not Elasticsearch. It doesn't do fuzzy matching, synonyms, or faceted search out of the box. But for straightforward full-text search on datasets under 100K records, it's remarkably capable and requires zero additional infrastructure.

DEV Community