We often see projects where a catalog query on 50,000 products takes 4 seconds instead of 40 milliseconds. EXPLAIN shows ALL instead of ref — the table is scanned entirely. This is a classic symptom: indexes were not added after data growth or were accidentally dropped during schema updates. The importance of indexes is well documented on Wikipedia. Over 10 years of work, we have learned to identify such problems in minutes and eliminate them with guaranteed results.
The problem is compounded by Bitrix using a complex schema with infoblocks, where data is scattered across dozens of tables. Without proper indexes, even a simple property filter turns into a full scan of millions of rows. We see this on every second project: the site worked fine up to 10,000 products, but after 50,000 it started to slow down. Owners often try to solve the problem with caching or server upgrades, but the root cause is missing indexes.
Which indexes are critical for infoblocks?
The storage structure of infoblock data is split across several tables. Below are the main ones with recommended indexes:
| Table | Purpose | Key fields for indexing |
|---|---|---|
b_iblock_element |
Main element records | IBLOCK_ID, ACTIVE, DATE_ACTIVE_FROM (composite) |
b_iblock_element_property |
Property values | IBLOCK_ID, IBLOCK_PROPERTY_ID, VALUE; IBLOCK_ELEMENT_ID, IBLOCK_PROPERTY_ID |
b_iblock_section |
Sections | IBLOCK_ID, LEFT_MARGIN, RIGHT_MARGIN |
b_catalog_price |
Commercial catalog prices | CATALOG_GROUP_ID, PRICE, CURRENCY |
b_search_content |
Search index | MODULE_ID, ITEM_ID |
In production, b_iblock_element_property easily reaches 10-30 million rows. A filter query by two properties without an index results in a full scan of both tables, causing second-long delays immediately.
How to diagnose slow queries?
First, enable the slow query log in MySQL/MariaDB:
SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 1; SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log'; Simultaneously, use Bitrix profiling via constants in dbconn.php:
define("DBDebug", true); define("DBDebugToFile", true); The log is written to bitrix/modules/main/tools/bx_sql.log. Enable it briefly in production — the file grows instantly. Analyze the top 10 slowest queries and review their plans via EXPLAIN.
How we add missing indexes?
Check for existing indexes using:
SHOW INDEX FROM b_iblock_element; SHOW INDEX FROM b_iblock_element_property; Add all necessary indexes in one batch:
ALTER TABLE b_iblock_element ADD INDEX ix_ie_iblock_active_date (IBLOCK_ID, ACTIVE, DATE_ACTIVE_FROM); ALTER TABLE b_iblock_element_property ADD INDEX ix_iep_iblock_prop_val (IBLOCK_ID, IBLOCK_PROPERTY_ID, VALUE), ADD INDEX ix_iep_element_prop (IBLOCK_ELEMENT_ID, IBLOCK_PROPERTY_ID); ALTER TABLE b_catalog_price ADD INDEX ix_cp_catalog_price (CATALOG_GROUP_ID, PRICE, CURRENCY); ALTER TABLE b_catalog_store_product ADD INDEX ix_csp_product_store (PRODUCT_ID, STORE_ID); ALTER TABLE b_stat_phrase_date ADD INDEX ix_spd_date_phrase (DATE1, PHRASE_ID); All changes are performed using pt-online-schema-change on tables larger than 1 GB to avoid locks.
Why caching does not solve the problem?
Caching hides symptoms but does not cure the cause. With every cache flush or first visit from a new user, the site will slow down again. Only properly configured indexes guarantee stable performance under any load.
Index maintenance is critical for performance
Bitrix sites with the statistic module accumulate millions of rows in b_stat_* tables. After mass deletions via the admin panel, indexes become fragmented, and data distribution statistics become outdated. We analyze fragmentation:
SELECT TABLE_NAME, ROUND(DATA_FREE/1024/1024, 2) AS free_mb, ROUND(DATA_LENGTH/1024/1024, 2) AS data_mb FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'bitrix_db' AND DATA_FREE > 10485760 ORDER BY DATA_FREE DESC; When fragmentation exceeds 20% of data, we run OPTIMIZE TABLE (full rebuild on InnoDB) or use pt-online-schema-change for online mode.
Typical symptoms and solutions
| Symptom | Likely cause | Solution |
|---|---|---|
| Catalog loads >3 sec | Missing index on IBLOCK_ID+ACTIVE | Add composite index |
| Property filter slow | No index on IBLOCK_PROPERTY_ID+VALUE | Add index |
| High CPU load during selects | Full table scans | Check EXPLAIN, add missing indexes |
| Sharp performance drop after statistic cleanup | Index fragmentation | Run OPTIMIZE TABLE |
What is included in our work?
- Audit of current indexes and query plans — identify bottlenecks.
- Addition and optimization of indexes — turnkey, with load testing.
- Automation of maintenance — configure a weekly
ANALYZE TABLEagent. - Documentation — record all changes, index schema, and recommendations.
- Post-project support — answer questions, adjust if needed.
We guarantee that after our tuning, the execution time of typical queries will decrease by 10–100 times. We confirm this with before/after test results. For example, on one project with a catalog of 200,000 products, we reduced the main page load time from 8 seconds to 0.2 seconds — 40 times faster than any caching optimization. Savings on server resources amounted to about 150,000 RUB per month, and the server rental cost decreased by 40%.
Case study example
Catalog of 200,000 products: main page loaded in 8 seconds. Cause — missing index on `IBLOCK_ID` and `ACTIVE` in `b_iblock_element`. After adding it, response time dropped to 0.2 seconds. CPU load decreased by 60%.Timeline and cost
Estimated work duration — from 2 to 5 business days depending on data volume and schema complexity. Cost is calculated individually after an audit. We quote the price transparently before work begins and do not hide additional options.
Order an index audit — we will identify bottlenecks in one day. Get a consultation: we will evaluate your project for free. 10+ years of experience and 50+ database optimization projects speak for themselves.
You can also study the official MySQL documentation on indexes for a deeper understanding.