Optimizing JOIN queries in 1C-Bitrix

JOIN Query Optimization in 1C-Bitrix

Bitrix actively uses JOINs in its SQL: a query to CIBlockElement::GetList() with property selection can join 8–12 tables. On small volumes unnoticeable. When b_iblock_element has 500,000 rows and b_iblock_element_property has 10 million, inefficient JOIN becomes multi-second operation.

How Bitrix Builds JOIN Queries

When you pass properties in $arSelectFields or filter by them, Bitrix adds JOINs to multiple tables depending on property storage type:

• Regular properties — LEFT JOIN b_iblock_element_property AS p1 ON p1.IBLOCK_ELEMENT_ID = be.ID AND p1.IBLOCK_PROPERTY_ID = N
• Multiple properties — each value separate row in b_iblock_element_property, JOIN returns multiple rows per element
• UTS properties (custom types) — separate table b_uts_iblock_N_single or b_uts_iblock_N_multiple, JOIN by IBLOCK_ELEMENT_ID
• List property (L) — additional JOIN to b_iblock_property_enum to get values

Problem: requesting 10 properties for 1,000 elements, Bitrix builds query with 10+ JOINs. MySQL performs nested loops — for each row from one table, it goes through related. Without indexes on JOIN keys, this is full scan on every iteration.

Main Causes of Slow JOINs in Bitrix

1. Missing indexes on join columns.

Most common case — JOIN by IBLOCK_ELEMENT_ID in b_iblock_element_property without index. Bitrix creates ix1 index on (IBLOCK_ELEMENT_ID) on install, but with data growth can be insufficient. EXPLAIN shows type=ALL on property table.

Check:

SHOW INDEX FROM b_iblock_element_property;
SHOW INDEX FROM b_uts_iblock_5_single;  -- for infoblock ID 5

For UTS tables, indexes aren't created automatically when adding properties via admin interface.

2. JOIN on string field VALUE with numeric data.

Field VALUE in b_iblock_element_property is type TEXT or VARCHAR(255). Filtering WHERE p.VALUE = '100' — string comparison, index on TEXT field inefficient, type cast on JOIN breaks index usage. For numeric properties, Bitrix duplicates data in field VALUE_NUM (FLOAT) — use it.

3. Querying multiple properties in single JOIN.

Selecting 5 multiple properties duplicates rows: if element has 3 values of property A and 4 values of property B — query returns 12 rows per element. MySQL processes Cartesian product, then groups. On 10,000 elements intermediate selection can be millions of rows.

Optimization: split into two queries — first get base data, then load multiple property values separately by ID array.

Query-Level Optimization

Move filtering before JOIN. If you need elements of certain section with specific property — first filter by b_iblock_element (with index on IBLOCK_SECTION_ID), then JOIN. MySQL should start with smallest selection.

Use STRAIGHT_JOIN to fix join order if optimizer chooses wrong plan:

$connection = \Bitrix\Main\Application::getConnection();
$result = $connection->query("
    SELECT STRAIGHT_JOIN be.ID, be.NAME, p.VALUE
    FROM b_iblock_element be
    INNER JOIN b_iblock_element_property p
        ON p.IBLOCK_ELEMENT_ID = be.ID AND p.IBLOCK_PROPERTY_ID = 42
    WHERE be.IBLOCK_ID = 5
      AND be.ACTIVE = 'Y'
      AND p.VALUE_NUM BETWEEN 1000 AND 5000
    ORDER BY be.SORT
    LIMIT 20
");

Replace LEFT JOIN with subquery or EXISTS. To check link existence (only need value or just presence) EXISTS is faster than LEFT JOIN with IS NOT NULL:

-- Slower: JOIN + group
SELECT be.* FROM b_iblock_element be
LEFT JOIN b_iblock_element_property p ON p.IBLOCK_ELEMENT_ID = be.ID AND p.IBLOCK_PROPERTY_ID = 10
WHERE p.ID IS NOT NULL;

-- Faster: EXISTS
SELECT be.* FROM b_iblock_element be
WHERE EXISTS (
    SELECT 1 FROM b_iblock_element_property p
    WHERE p.IBLOCK_ELEMENT_ID = be.ID AND p.IBLOCK_PROPERTY_ID = 10
);

D7 ORM Optimization

D7 allows controlling which tables are included via runtime parameter and explicit relation setup. With HL infoblocks (Highload), D7 generates cleaner queries without unnecessary JOINs to b_iblock_element_property.

// Avoid JOIN to properties table, work directly with HL table
$result = \Bitrix\Highloadblock\HighloadBlockTable::compileEntity($hlBlock)
    ->getDataClass()::getList([
        'select' => ['ID', 'UF_PRODUCT_ID', 'UF_PRICE'],
        'filter' => ['>=UF_PRICE' => 1000, '<=UF_PRICE' => 5000],
        'limit'  => 100,
    ]);

HL infoblocks store data in separate table without JOIN to b_iblock_element_property — for high-load properties (technical specs, large catalogs), this is proper architecture.

Indexes for JOIN Optimization

Work Timeline

JOIN queries in Bitrix are consequence of architectural decision to store all properties in universal table. At small volumes works fine. As data grows, either add indexes or change storage schema to HL infoblocks or custom tables.