Optimizing JOIN Queries in 1C-Bitrix: Indexes, D7 ORM, and HL-Blocks

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_single or b_uts_iblock_N_multiple, JOIN on IBLOCK_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.

How We Optimize: Step-by-Step Process

1. EXPLAIN analysis — find slow queries, identify missing indexes.
2. Add indexes — create composite indexes for JOIN and filter fields.
3. Refactor property selection — split queries with multiple properties, replace LEFT JOIN with EXISTS.
4. Migrate to D7 ORM — for critical sections, use D7 ORM with explicit relations.
5. 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

Process and Timeline

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.

80% of Bitrix sites slow down due to one table

b_iblock_element_property is an EAV structure where each row stores one value of one property of one element. A catalog of 50,000 products with 30 properties yields 1.5 million rows. The smart filter performs a JOIN of this table with b_iblock_element on five properties, and MySQL performs a full table scan for 3–5 seconds. Our experience shows that without intervention in this table, site acceleration is impossible. We take on projects where load time has dropped to 8–10 seconds and bring TTFB back to <200 ms within 1–2 weeks. Site speed optimization begins with an audit of slow queries and ends with a comprehensive turnkey infrastructure overhaul.

Contact us for an audit — we will identify bottlenecks within 2 hours and propose a concrete plan.

How to achieve TTFB below 200 ms?

Server optimization is the first step. Nginx configuration goes beyond simple gzip. Specifically:

• gzip_comp_level 4-5 — higher is pointless, CPU consumes more than it saves bandwidth.
• brotli on with brotli_static on for precompressed files.
• HTTP/2 with http2_max_concurrent_streams 128.
• fastcgi_cache for PHP responses — caching at Nginx level, bypassing PHP-FPM entirely.
• worker_processes auto, worker_connections according to the number of simultaneous connections.

PHP-FPM tuning: choose between pm = dynamic and pm = static. Static mode works best for dedicated servers with predictable load because it avoids forking overhead. Dynamic saves RAM under low traffic. Calculate pm.max_children as (available RAM - RAM for MySQL/Redis) / average process consumption. For OPcache set memory_consumption=256, max_accelerated_files=20000, and validate_timestamps=0 in production (restart PHP-FPM on deploy).

MySQL/MariaDB: the main bottleneck is almost always the database. Enable slow_query_log with a threshold of 0.5 sec and analyze every query via EXPLAIN. Set innodb_buffer_pool_size to 70–80% of available RAM on a dedicated server. Create composite indexes for faceted search: (IBLOCK_ID, IBLOCK_PROPERTY_ID, VALUE) on b_iblock_element_property. Run OPTIMIZE TABLE b_iblock_element_property after mass operations.

How to configure three-level caching?

Managed component cache. Set TTL individually for each component. Catalog — 3600 sec, news feed — 300 sec, banners — 86400. The same TTL everywhere guarantees either outdated data or useless cache.

Composite cache. The bitrix:composite technology lets Nginx serve ready HTML from a file; PHP is not executed. Dynamic zones (cart, authorization) are loaded via AJAX request through CBitrixComponent::setFrameMode(true). TTFB drops below 50 ms. However, not all components are compatible; $APPLICATION->ShowPanel() and direct output via echo break the composite. We check every page through the panel 'Performance → Composite Site'. According to Bitrix official documentation on composite cache, this is the most effective caching method for high‑load projects.

Comparison: composite cache is 10–20 times faster than managed cache in time to first byte.

Memcached / Redis. Transfer cache from the file system: sessions go to Redis (session.save_handler = redis) — 10–50 times faster than files, plus cluster support. Component cache goes to Memcached via .settings.php: 'cache' => ['type' => 'memcache']. Also enable ORM query cache so identical GetList() calls don't hit MySQL on every request.

What is the fastest way to optimize Bitrix database?

Default MySQL settings are insufficient. Indexes — composite for faceted search, covering for frequent queries. MySQL responds from the index without accessing the data. Partial indexes (MariaDB) for filtering by ACTIVE = 'Y'. Audit unused indexes — each slows down INSERT/UPDATE.

Partitioning. For tables with millions of rows: b_stat_session, b_search_content_stem, and highload-blocks with history. Partition by date — a query for 'orders in a month' does not scan three years of data. Partitioning also solves the problem of concurrent queries during exchange with 1С via CommerceML.

Real case: a catalog of 200,000 products, 50 properties. Filtering by 10 properties took 12 seconds. After creating composite indexes on (IBLOCK_ID, IBLOCK_PROPERTY_ID, VALUE) and partitioning b_iblock_element_property by IBLOCK_ID, execution time dropped to 0.3 seconds. MySQL load decreased by 40 times.

Cleanup. Over a year or two, any database accumulates: outdated search index, expired records in b_cache_tag, history in b_iblock_element_prop_s*, logs in b_event_log taking gigabytes. We set up regular cleanup via agents.

Frontend and CDN

Images account for 60–80% of page weight. Convert to WebP via CFile::ResizeImageGet() with BX_RESIZE_IMAGE_PROPORTIONAL + conversion. Use srcset + sizes — never load a 3000px image into a 400px block. Add loading="lazy" for everything below the fold. AVIF offers another 20–30% savings vs WebP.

CSS/JS optimization: use the built-in Bitrix module to merge and minify via 'Settings → CSS/JS Optimization'. Apply PurgeCSS / UnCSS — in a typical Bitrix project, 60–70% of CSS is unused. Use defer / async for non‑critical JS and inline critical CSS in <head> for instant FCP.

Fonts: add <link rel="preload" as="font" crossorigin> for the main font. Set font-display: swap — text visible immediately. Subset via pyftsubset — keep only Cyrillic + Latin, file size reduces by 3–5 times.

CDN: Cloudflare, BunnyCDN, AWS CloudFront, or Russian providers (Selectel CDN, VK Cloud CDN). Serve static assets (CSS, JS, images, fonts) via CDN with Cache-Control: public, max-age=31536000, immutable for files with a hash. Use on‑the‑fly image optimization (imgproxy, Cloudflare Polish) without load on origin.

Why is load testing necessary?

Not synthetic benchmarks, but real scenarios: k6 / wrk to simulate routes — catalog → filtering → product card → cart → checkout. Measure RPS, response time (p50, p95, p99), error rate. Use Xdebug (callgrind) or Blackfire for PHP profiling to find bottlenecks. The test result gives an objective picture of where it actually slows down, not where it 'seems'. After optimization, run again to record improvements.

Results

What is included in the work?

1. Current performance audit — analysis of slow queries, PHP profiling, check of caching, CDN, server settings.
2. Server configuration — Nginx, PHP-FPM, MySQL, Redis/Memcached, OPcache.
3. Caching optimization — managed cache, composite site, TTL configuration, tagged caching.
4. Database work — index creation, partitioning, cleanup, EAV table reorganization.
5. Frontend — images (WebP/AVIF), CSS/JS (minification, deferred), fonts (preload, subsetting).
6. CDN — connection, caching rule setup.
7. Load testing — real user scenarios, metric report.
8. Documentation — description of all changes, recommendations for further maintenance.
9. Guarantee — support for 1 month after delivery, ensuring all optimizations are stable.

Monitoring

Without monitoring, everything degrades in six months. A new module, uncleared logs, a template change — and speed returns to original. Use web-vitals API for Real User Monitoring from actual visitors. Set up synthetic monitoring with Pingdom or UptimeRobot for regular checks from different locations. Configure alerts — TTFB > 500 ms or LCP > 3 sec triggers notification.

Timelines and cost

Cost is calculated individually after the audit. Get a consultation for your project — we will evaluate the current state and propose an acceleration plan with specific timelines and budget. We are a team with 12+ years of experience in Bitrix, having completed over 300 site speed optimization projects. Contact us to start the performance audit today.