Setting up MySQL full-text indexes for 1C-Bitrix

Configuring MySQL Full-Text Indexes for 1C-Bitrix

The standard Bitrix search operates through the bitrix:search.page component and the search module. It builds its own index in the b_search_content table — all information block elements, pages, and forum posts are indexed there. Queries run via LIKE '%query%', which at volumes exceeding 100,000 elements turns into a full table scan degrading to 5–15 seconds. The solution is MySQL FULLTEXT indexes applied directly to information block tables or to b_search_content.

How FULLTEXT Works in MySQL

A FULLTEXT index is built on top of text columns (CHAR, VARCHAR, TEXT). Queries use MATCH() AGAINST(). Two modes: IN NATURAL LANGUAGE MODE (relevance ranking) and IN BOOLEAN MODE (operator support: +required, -exclude, *prefix).

MySQL FULLTEXT limitations:

• Minimum word length by default: ft_min_word_len = 4 (for MyISAM), innodb_ft_min_token_size = 3 (for InnoDB). Shorter words are not indexed.
• Stop words (innodb_ft_server_stopword_table) — must be disabled or reconfigured.
• Russian-language indexing requires the correct encoding (utf8mb4) and ideally an external parser (ngram for InnoDB, or Sphinx/Manticore as an alternative).

Configuring my.cnf for Russian FULLTEXT

[mysqld]
# Minimum token length
innodb_ft_min_token_size = 2

# Disable default stop words (they are English-oriented)
innodb_ft_enable_stopword = OFF

# Enable the ngram parser for CJK and short word support
# (alternative: use ngram in the DDL statement)

After changing innodb_ft_min_token_size, all FULLTEXT indexes must be rebuilt — a simple MySQL restart is not sufficient.

Creating a FULLTEXT Index on b_search_content

The b_search_content table is the central search point in Bitrix. Key columns: TITLE, BODY.

-- Check existing indexes
SHOW INDEX FROM b_search_content;

-- Create FULLTEXT index
ALTER TABLE b_search_content
    ADD FULLTEXT INDEX ft_search_content (TITLE, BODY)
    WITH PARSER ngram;

WITH PARSER ngram — a parser built into MySQL 5.7+ that splits text into bigrams/trigrams. Works well for Cyrillic text without requiring external tools.

The ngram token size is configurable:

[mysqld]
ngram_token_size = 2  # optimal for Russian

Overriding Search in Bitrix

The standard bitrix:search.page does not use FULLTEXT — it operates through the Bitrix ORM with LIKE. To enable FULLTEXT, we override the query in a custom wrapper component or via the OnBeforeIBlockElementGetList event.

Minimal custom FULLTEXT search:

namespace Local\Search;

class FulltextSearcher
{
    private \Bitrix\Main\DB\Connection $db;

    public function __construct()
    {
        $this->db = \Bitrix\Main\Application::getConnection();
    }

    public function search(string $query, int $page = 1, int $limit = 20): array
    {
        $query   = $this->sanitizeQuery($query);
        $offset  = ($page - 1) * $limit;

        // BOOLEAN MODE with prefix search
        $boolQuery = '+' . implode('* +', explode(' ', $query)) . '*';

        $sql = "
            SELECT
                sc.ID,
                sc.TITLE,
                sc.URL,
                sc.MODULE_ID,
                sc.ITEM_ID,
                MATCH(sc.TITLE, sc.BODY) AGAINST (? IN BOOLEAN MODE) AS relevance
            FROM b_search_content sc
            WHERE
                MATCH(sc.TITLE, sc.BODY) AGAINST (? IN BOOLEAN MODE)
                AND sc.SITE_ID = ?
                AND sc.PUBLIC = 'Y'
            ORDER BY relevance DESC
            LIMIT ? OFFSET ?
        ";

        $result = $this->db->query($sql, [$boolQuery, $boolQuery, SITE_ID, $limit, $offset]);

        $rows = [];
        while ($row = $result->fetch()) {
            $rows[] = $row;
        }

        return $rows;
    }

    public function count(string $query): int
    {
        $boolQuery = '+' . implode('* +', explode(' ', $query)) . '*';

        $result = $this->db->query(
            "SELECT COUNT(*) AS cnt FROM b_search_content
             WHERE MATCH(TITLE, BODY) AGAINST (? IN BOOLEAN MODE)
             AND SITE_ID = ? AND PUBLIC = 'Y'",
            [$boolQuery, SITE_ID]
        );

        return (int)$result->fetch()['cnt'];
    }

    private function sanitizeQuery(string $query): string
    {
        // Strip FULLTEXT operators, keep only words
        $query = preg_replace('/[+\-><()\~*"@]+/', ' ', $query);
        $query = preg_replace('/\s+/', ' ', trim($query));

        return mb_substr($query, 0, 255);
    }
}

Custom Search Component

The component template uses FulltextSearcher instead of the standard module:

// /local/components/local/search.fulltext/component.php
if (!defined('B_PROLOG_INCLUDED') || B_PROLOG_INCLUDED !== true) die();

$query = trim($_GET['q'] ?? '');

if (mb_strlen($query) < 2) {
    $this->arResult['ITEMS'] = [];
    $this->arResult['TOTAL'] = 0;
    $this->IncludeComponentTemplate();
    return;
}

$searcher = new \Local\Search\FulltextSearcher();
$page     = max(1, (int)($_GET['PAGEN_1'] ?? 1));

$this->arResult['ITEMS'] = $searcher->search($query, $page);
$this->arResult['TOTAL'] = $searcher->count($query);
$this->arResult['QUERY'] = htmlspecialchars($query);
$this->arResult['PAGE']  = $page;

$this->SetResultCacheKeys([]); // do not cache search results

$this->IncludeComponentTemplate();

FULLTEXT on Information Block Tables

For catalog-only search, it is more efficient to index the information block tables directly: b_iblock_element (NAME, DETAIL_TEXT) and b_iblock_section (NAME).

ALTER TABLE b_iblock_element
    ADD FULLTEXT INDEX ft_iblock_element_search (NAME, SEARCHABLE_CONTENT)
    WITH PARSER ngram;

The SEARCHABLE_CONTENT column is populated by Bitrix when an element is saved and contains consolidated text for search purposes.

Index Monitoring

-- InnoDB FULLTEXT index statistics
SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE;
SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;

-- Force rebuild
SET GLOBAL innodb_optimize_fulltext_only = ON;
OPTIMIZE TABLE b_search_content;
SET GLOBAL innodb_optimize_fulltext_only = OFF;

Scope of Work

• Audit of the current search, performance benchmarking
• my.cnf configuration: ngram_token_size, stop word disabling
• Creating FULLTEXT indexes on b_search_content and/or information block tables
• Developing a custom search component with FULLTEXT queries
• Rebuilding the Bitrix search index (BXSearch::reindex())
• Load testing before and after

Timeline: index setup and custom component — 1–2 weeks.