Optimizing Highload Blocks for Large Volumes of 1C-Bitrix Data

Optimizing Highload Blocks for Large Data Volumes in 1C-Bitrix

Highload blocks (the highloadblock module) are a Bitrix mechanism for storing arbitrary data in separate tables outside the information block architecture. Common use cases: event logs, product catalogs with non-standard structures, user profiles, accumulative data (order history, analytics, queues). With fewer than 50,000–100,000 rows everything works fine. At 1–10 million rows, problems begin: the Bitrix ORM generates suboptimal queries, indexes do not cover actual query patterns, and JOINs become slow.

Where Performance Breaks Down

The main anti-patterns when working with Highload:

1. Missing necessary indexes. A Highload block creates a table with a primary key ID and auto-increment. Custom UF_* fields are not indexed automatically. A query such as getList(['filter' => ['UF_PRODUCT_ID' => 123]]) against a million rows is a full table scan.

*2. SELECT -style queries. The Bitrix ORM selects all fields by default. If a record has 30 UF fields including TEXT and FILE types, this is an expensive query even with a small result set.

3. Unlimited queries without pagination. DataManager::getList() without a limit will load all records into PHP memory.

4. Related tables via Reference. If a Highload block is linked to another Highload or an information block through Reference fields, the ORM builds a JOIN that destroys performance without proper indexes.

5. Frequent UPDATEs on unindexed fields. Typical for status fields and counters.

Diagnostics: Finding the Bottleneck

Enable the MySQL slow query log:

[mysqld]
slow_query_log        = 1
slow_query_log_file   = /var/log/mysql/slow.log
long_query_time       = 1
log_queries_not_using_indexes = 1

Enable Bitrix query profiling (dev environment only):

define('BX_SECURITY_SHOW_MESSAGE', true);
\Bitrix\Main\Diag\SqlTracker::start();

// ... your code

$tracker = \Bitrix\Main\Diag\SqlTracker::getInstance();
foreach ($tracker->getQueries() as $query) {
    if ($query->getTime() > 0.1) {
        echo $query->getSql() . ' — ' . round($query->getTime() * 1000) . 'ms' . PHP_EOL;
    }
}

Optimization 1: Proper Indexes

Add indexes to the Highload table via direct SQL — in an install agent or a migration script:

// Determine the Highload block table name
$hlBlock = \Bitrix\Highloadblock\HighloadBlockTable::getList([
    'filter' => ['NAME' => 'ProductCatalog'],
])->fetch();

$hlEntity = \Bitrix\Highloadblock\HighloadBlockTable::compileEntity($hlBlock);
$tableName = $hlEntity->getDBTableName();

$connection = \Bitrix\Main\Application::getConnection();

// Index on a field used in filters
$connection->queryExecute(
    "CREATE INDEX IF NOT EXISTS idx_product_id ON {$tableName} (UF_PRODUCT_ID)"
);

// Composite index for a typical query: status + date
$connection->queryExecute(
    "CREATE INDEX IF NOT EXISTS idx_status_date ON {$tableName} (UF_STATUS, UF_DATE_CREATE)"
);

// Full-text index
$connection->queryExecute(
    "CREATE FULLTEXT INDEX IF NOT EXISTS ft_name ON {$tableName} (UF_NAME) WITH PARSER ngram"
);

Optimization 2: Explicit Field Selection

Never request select: ['*'] or an empty select array:

// Bad — selects all fields
$result = ProductCatalogTable::getList([
    'filter' => ['UF_CATEGORY_ID' => $categoryId],
]);

// Good — only what is needed
$result = ProductCatalogTable::getList([
    'select' => ['ID', 'UF_NAME', 'UF_PRICE', 'UF_ACTIVE'],
    'filter' => ['UF_CATEGORY_ID' => $categoryId, 'UF_ACTIVE' => 1],
    'order'  => ['UF_SORT' => 'ASC'],
    'limit'  => 50,
    'offset' => ($page - 1) * 50,
]);

Optimization 3: Result Caching

Highload data caches well using Bitrix\Main\Data\Cache:

class CachedProductCatalog
{
    private const CACHE_TAG = 'hl_product_catalog';
    private const CACHE_TTL = 3600;

    public function getByCategory(int $categoryId): array
    {
        $cache    = \Bitrix\Main\Data\Cache::createInstance();
        $cacheKey = 'hl_catalog_cat_' . $categoryId;

        if ($cache->initCache(self::CACHE_TTL, $cacheKey, '/hl/catalog/')) {
            return $cache->getVars();
        }

        $cache->startDataCache();

        $result = $this->fetchFromDb($categoryId);

        // Tagged cache — invalidated when any element changes
        $tagCache = new \Bitrix\Main\Data\TaggedCache();
        $tagCache->startTagCache('/hl/catalog/');
        $tagCache->registerTag(self::CACHE_TAG . '_' . $categoryId);
        $tagCache->endTagCache();

        $cache->endDataCache($result);

        return $result;
    }

    // Invalidation on data change
    public static function clearCache(int $categoryId): void
    {
        $tagCache = new \Bitrix\Main\Data\TaggedCache();
        $tagCache->clearByTag(self::CACHE_TAG . '_' . $categoryId);
    }
}

Cache invalidation from a Highload event handler:

\Bitrix\Main\EventManager::getInstance()->addEventHandler(
    'highloadblock',
    'ProductCatalogOnAfterUpdate',
    function (\Bitrix\Main\Event $event) {
        $fields = $event->getParameter('fields');
        if (isset($fields['UF_CATEGORY_ID'])) {
            CachedProductCatalog::clearCache((int)$fields['UF_CATEGORY_ID']);
        }
    }
);

Optimization 4: Direct SQL for Aggregation

The Bitrix ORM does not always generate efficient SQL for aggregate queries. For COUNT, SUM, and GROUP BY against large tables — go direct:

class HlStatistics
{
    public function getOrderCountByStatus(string $tableName): array
    {
        $connection = \Bitrix\Main\Application::getConnection();
        $tableName  = $connection->getSqlHelper()->forSql($tableName);

        $result = $connection->query(
            "SELECT UF_STATUS, COUNT(*) as cnt, SUM(UF_AMOUNT) as total
             FROM {$tableName}
             WHERE UF_DATE_CREATE >= DATE_SUB(NOW(), INTERVAL 30 DAY)
             GROUP BY UF_STATUS
             ORDER BY cnt DESC"
        );

        $rows = [];
        while ($row = $result->fetch()) {
            $rows[$row['UF_STATUS']] = [
                'count' => (int)$row['cnt'],
                'total' => (float)$row['total'],
            ];
        }

        return $rows;
    }
}

Optimization 5: Partitioning for Chronological Data

If a Highload block stores logs or events with a date — range partitioning by date dramatically speeds up period queries:

ALTER TABLE b_hl_event_log
    PARTITION BY RANGE (YEAR(UF_DATE_CREATE) * 100 + MONTH(UF_DATE_CREATE)) (
        PARTITION p_2024_01 VALUES LESS THAN (202402),
        PARTITION p_2024_02 VALUES LESS THAN (202403),
        -- ...
        PARTITION p_future VALUES LESS THAN MAXVALUE
    );

Partitions are created in an init script or agent; new partitions are added in advance by an agent that runs at the start of each month.

Optimization 6: Redis for Counters and Queues

If a Highload block is used as a task queue or counter store — frequent UPDATEs to the same row create lock contention. Move counters to Redis:

class HlCounter
{
    public function increment(string $key, int $amount = 1): void
    {
        $redis = \Bitrix\Main\Data\Cache::createInstance();
        // Or directly via \Local\Redis\Client
        $redis->increment('hl_counter_' . $key, $amount);
    }

    // Agent flushes Redis counters to Highload once per minute
    public function flushToDb(): void
    {
        $keys = $this->redis->keys('hl_counter_*');
        foreach ($keys as $key) {
            $value  = $this->redis->get($key);
            $hlKey  = str_replace('hl_counter_', '', $key);
            $this->updateHighloadRecord($hlKey, $value);
            $this->redis->del($key);
        }
    }
}

Benchmarks: Impact of Each Optimization

Scope of Work

• Audit of Highload blocks: structure, data volume, typical queries
• Slow query log analysis: top offending queries
• Adding single and composite indexes matched to real filter patterns
• Code refactoring: explicit selects, limits, pagination
• Implementing tagged cache for heavy queries
• (If needed) Partitioning chronological tables
• Load testing with A/B comparison before and after

Timeline: audit + indexes + cache — 2–3 weeks. Full optimization with partitioning and code refactoring — 4–8 weeks.