SQL Query Optimization via EXPLAIN Analysis in 1C-Bitrix
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 discovering a slow query in slow query log.
How to Read EXPLAIN
EXPLAIN SELECT * FROM b_iblock_element
WHERE IBLOCK_ID = 5 AND ACTIVE = 'Y'
ORDER BY SORT;
Key output columns:
| Column | What to Look For |
|---|---|
type |
ALL = full scan (bad), ref/range/const = uses index |
key |
Which index optimizer chose, NULL = no index |
rows |
Estimate of rows to examine. 100,000+ on simple selection — problem |
Extra |
Using filesort = memory/disk sort. Using temporary = temporary table |
EXPLAIN ANALYZE (MySQL 8.0+, MariaDB 10.9+) executes the query and shows real time:
EXPLAIN ANALYZE SELECT ...;
Characteristic Bitrix Problems
Using filesort on b_iblock_element. Query sorts by SORT, but index doesn't cover combination (IBLOCK_ID, ACTIVE, SORT). Solution: composite index:
ALTER TABLE b_iblock_element
ADD INDEX ix_iblock_active_sort (IBLOCK_ID, ACTIVE, SORT);
rows = 500,000 on query to b_iblock_element_property. Filtering by property value without index on (IBLOCK_PROPERTY_ID, VALUE). For VARCHAR field VALUE — index by prefix VALUE(64).
Using temporary on GROUP BY. Occurs in faceted filter queries. Bitrix facet builds optimized tables b_iblock_find_* — if not rebuilt after adding properties, queries bypass them.
Workflow
- Get slow query from slow query log or Bitrix SQL tracker
- Run
EXPLAIN— findtype=ALLorrows> 10,000 on point selection - Identify missing index or inefficient JOIN
- Add index, repeat
EXPLAIN— ensuretypechanged - Check real time on production data
After adding index, MySQL might not use it — if table statistics are stale, run ANALYZE TABLE b_iblock_element to recalculate.