Imagine: a catalog page with filter loads in 7 seconds. Users leave, conversion drops. EXPLAIN shows 15 JOINs to the property table — classic Bitrix with data growth. This situation is familiar to anyone working with Bitrix catalogs. We've learned to find bottlenecks and eliminate them without risk to functionality. Our team helps dozens of projects speed up selections by 5–20 times without changing the platform. Over the past years, we've performed more than 200 optimizations, and in 80% of cases, the root cause is incorrect indexes and excessive JOINs. Let's break down how to fix this, so you can save on server resources and boost catalog speed to 100 ms.
Contact us for a free audit of your catalog — evaluation of JOIN queries and recommendations.
How Bitrix Builds JOIN Queries
Passing properties in $arSelectFields or filtering by them causes Bitrix to add JOINs to several tables depending on the 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 as a separate row in
b_iblock_element_property, JOIN returns multiple rows per element - UTS properties — separate table
b_uts_iblock_N_singleorb_uts_iblock_N_multiple, JOIN onIBLOCK_ELEMENT_ID - List property — additional JOIN to
b_iblock_property_enum
The problem: if you request 10 properties for 1000 elements, Bitrix builds a query with 10+ JOINs. MySQL executes nested loops — for each row from one table, it goes through the related table. Without indexes on JOIN keys, this is a full scan on each iteration. More on SQL JOIN.
Three Main Causes of Slow JOIN Queries
Missing Indexes on Join Columns
The most common case — JOIN on IBLOCK_ELEMENT_ID in b_iblock_element_property without a suitable index. Bitrix creates an index ix1 on (IBLOCK_ELEMENT_ID) during installation, but with data growth this is insufficient. EXPLAIN will show type=ALL on the property table.
Check indexes:
SHOW INDEX FROM b_iblock_element_property; SHOW INDEX FROM b_uts_iblock_5_single; -- for infoblock ID 5 For UTS tables, indexes are not created automatically when adding properties through the administrative interface.
JOIN on String Field VALUE with Numeric Data
The VALUE field in b_iblock_element_property is of type TEXT or VARCHAR(255). Filtering WHERE p.VALUE = '100' is a string comparison; an index on a TEXT field is inefficient, and type conversion in JOIN breaks index usage. For properties with numeric values, Bitrix duplicates data in the VALUE_NUM (FLOAT) field — use it.
Querying Multiple Properties in a Single JOIN
Fetching 5 multiple properties duplicates rows: if an element has 3 values for property A and 4 for property B, the query returns 12 rows per element. MySQL processes a Cartesian product, then groups. For 10,000 elements, the intermediate result can be millions of rows.
Optimization: split into two queries — first get the main data, then load multiple property values separately with an array of IDs.
When to Migrate Properties to HL-Blocks?
HL-blocks store data in a separate table without JOINs to b_iblock_element_property. If a property is actively used in filters, has millions of values, or requires complex sorting — that's a signal to migrate. For example, for technical characteristics (weight, height), an HL-block provides up to 10x performance gain on filtered selections. No need to change catalog logic: just configure binding via the UF_PRODUCT_ID field. Refactoring with D7 ORM speeds up queries by 10x compared to standard GetList, and HL-blocks give up to 20x gain on filters.
How to Diagnose with EXPLAIN
We use EXPLAIN for every slow query. Pay attention to the type column: if you see ALL (full scan) or index (index scan), look to add an index or rewrite the query. Optimal values are ref or eq_ref.
EXPLAIN Example
```sql EXPLAIN SELECT 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' ORDER BY be.SORT LIMIT 20; ```How We Optimize: Step-by-Step Process
- EXPLAIN analysis — find slow queries, identify missing indexes.
- Add indexes — create composite indexes for JOIN and filter fields.
- Refactor property selection — split queries with multiple properties, replace LEFT JOIN with EXISTS.
- Migrate to D7 ORM — for critical sections, use D7 ORM with explicit relations.
- Testing — measure execution time before and after, record results.
D7 ORM: Modern Way to Fight JOINs
D7 ORM allows controlling which tables are included in JOINs via the runtime parameter and explicit relations. When working with HL-blocks, D7 generates cleaner queries without unnecessary JOINs to b_iblock_element_property. Compare: standard GetList for 10 properties — 10 JOINs; D7 ORM with HL-block — 0 JOINs for 10,000 elements.
// Avoid JOIN to property 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, ]); More in D7 ORM documentation.
Comparison of Property Storage Approaches
| Storage | JOIN to b_iblock_element_property | Performance at 1M elements | Indexing |
|---|---|---|---|
| Standard properties | Yes, multiple JOINs | Low (2–5 s per query) | Only by IBLOCK_ELEMENT_ID |
| UTS table | Yes, one JOIN | Medium (0.5–2 s) | Custom possible |
| HL-block | No | High (10–50 ms) | Flexible indexing |
Process and Timeline
| Task | Timeline | Effect |
|---|---|---|
| EXPLAIN analysis of JOINs, add indexes | 2–3 days | 5–20x speedup on problematic queries |
| Refactor property selection (split subqueries) | 3–5 days | Eliminate Cartesian product |
| Migrate heavily used properties to HL-blocks | 1–2 weeks | Eliminate JOINs to b_iblock_element_property |
| Comprehensive catalog optimization | 2–3 weeks | Catalog page < 100 ms instead of 2–5 s |
Pricing is calculated individually. Contact us for a free audit — we will assess the scope and provide preliminary timelines.
What's Included
- Audit of current queries and indexes (EXPLAIN, server profile analysis)
- Adding and optimizing indexes on key fields
- Refactoring property selection (splitting multiple JOINs, replacing with subqueries)
- Migrating heavily used properties to HL-blocks with data migration
- Documenting changes and maintenance recommendations
- Training your team on D7 ORM and cache configuration
- Post-project support and maintenance
Why Trust Us?
Over 10 years developing on Bitrix, 200+ performance optimization projects. Certified 1C-Bitrix partner, guaranteeing a transparent process and measurable results. We use best practices described in the official documentation.
JOIN queries in Bitrix are a consequence of the architectural decision to store all properties in a universal table. At small volumes, this works. As data grows, you need either to add indexes or change the storage schema to HL-blocks or custom tables. We'll help you choose the optimal path. Request a consultation — we will analyze your project and offer the best solution. Contact us for a free audit.