SQL Query Optimization via EXPLAIN Analysis for 1C-Bitrix

Slow catalog or admin interface performance is a common complaint. After ten years of optimizations, we've learned: the root cause almost always lies in inefficient SQL queries. For example, a product list page loads 8–10 seconds because MySQL scans millions of rows in b_iblock_element, while developers never check the slow query log. Clients lose conversions, and server resources are wasted. Our task is to find bottlenecks through EXPLAIN analysis within 2–5 days and fix them.

EXPLAIN is a MySQL command that shows the query execution plan: which tables are scanned, whether indexes are used, how many rows are processed. It's the primary tool after spotting a slow query in the slow query log. Analysis is performed on a database copy or during low-load hours to avoid impacting the live site.

After a decade working with Bitrix, we see the same pattern: in most projects, blindly adding indexes without analysis yields temporary improvements, and problems return within a week. Only systematic EXPLAIN analysis followed by code and caching corrections provides long-term results. Clients save from $5,500 per year on server resources after such optimization.

How to Read EXPLAIN

EXPLAIN SELECT * FROM b_iblock_element WHERE IBLOCK_ID = 5 AND ACTIVE = 'Y' ORDER BY SORT; 

Key output columns:

For more precise diagnostics, use EXPLAIN ANALYZE (MySQL 8.0+, MariaDB 10.9+), which executes the query and shows actual time:

EXPLAIN ANALYZE SELECT ...; 

How EXPLAIN Reveals Slow Queries

In the slow query log, we look for queries with execution time > 1 second. For each, we run EXPLAIN. If we see type=ALL or rows > 10,000 for a point query, that query is a candidate for optimization. For example, on one project we found a query against b_iblock_element_property with rows=1,200,000. After adding an index, rows dropped to 1,200, and execution time fell from 3 seconds to 0.01 seconds.

Common Bitrix Issues

Using filesort on b_iblock_element is a frequent finding. The query sorts by SORT, but the index doesn't cover the combination (IBLOCK_ID, ACTIVE, SORT). Solution: composite index:

ALTER TABLE b_iblock_element ADD INDEX ix_iblock_active_sort (IBLOCK_ID, ACTIVE, SORT); 

After adding the index, EXPLAIN shows type=ref and Extra without Using filesort. Execution time drops from seconds to milliseconds.

rows = 500,000 on a query to b_iblock_element_property happens when filtering by property value without an index on (IBLOCK_PROPERTY_ID, VALUE). For VARCHAR field VALUE, use a prefix index VALUE(64). This reduces scanned rows to hundreds.

Using temporary with GROUP BY appears in facet filter queries. Bitrix facets build optimized tables b_iblock_find_* — if they are not rebuilt after adding properties, queries bypass them.

Diagnosing ALL Scans

type=ALL is the worst case: MySQL scans the entire table. In Bitrix, this often occurs in queries without conditions or with conditions on non-indexed fields. We immediately add missing indexes. For instance, b_iblock_element should have an index on (IBLOCK_ID, ACTIVE), b_iblock_element_property on (IBLOCK_ELEMENT_ID, IBLOCK_PROPERTY_ID). If the problem is in component code, we rewrite the query using CIBlockElement::GetList with correct parameters.

Why EXPLAIN Analysis Beats Intuitive Approach

Experience shows: developers often add indexes 'by eye' without checking the query plan. EXPLAIN gives an objective picture. Compare for yourself: without index, the query scans 500,000 rows in 2 seconds; with index, 500 rows in 0.002 seconds. A 250x difference. Only this way guarantee results.

What Our Work Includes

1. Audit slow query log — collect all slow queries over a week. We use pt-query-digest or built-in MySQL tools.
2. EXPLAIN analysis of each suspicious query — identify type=ALL, Using filesort, Using temporary.
3. Design indexes — taking into account load and data structure. Sometimes an index can degrade insert performance, so we evaluate trade-offs.
4. Add indexes and rewrite queries — if an index doesn't help, we fix component code or the query (e.g., change ORDER BY or add FORCE INDEX).
5. Re-run EXPLAIN — verify that type changed and rows dropped.
6. Test on production data — measure execution time before and after. Use profiling via EXPLAIN ANALYZE.
7. Documentation — record all changes and recommendations for ongoing monitoring.

Before and After Comparison

Timeframes and Cost

Optimization of one typical query (analysis + fix) takes 2 to 5 hours. Full project audit takes 2 to 5 working days. The cost for optimizing one query ranges from 5,000 to 15,000 rubles. Contact us for a preliminary audit of your slow query log. Order SQL optimization via EXPLAIN analysis and get a free analysis of one query.

Additional information: Wikipedia: EXPLAIN