Professional Database Index Optimization for 1C-Bitrix

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:

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

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 TABLE agent.
• 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%.

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.

What Professional 1C-Bitrix Installation Includes

We start by checking innodb_buffer_pool_size. The default MySQL value (128 MB) is a death sentence for an online store with a catalog of 10,000+ items. We set 70–80% of available RAM on a dedicated server, 50% on VPS. This single setting speeds up the site by 2–3 times compared to the default. We'll assess your project in one day — get a consultation. Contact us to order turnkey installation with performance guarantee.

How to Choose Hosting and Edition for 1C-Bitrix Installation?

BitrixVM is a virtual machine with a pre-installed stack: nginx + Apache, PHP-FPM, MySQL/MariaDB, Sphinx, Push server. For VPS — the best start. Everything is already configured for Bitrix, including OPcache, log rotation, and firewall. Management via web panel on port 8890. Bitrix documentation recommends starting with BitrixVM for predictable performance.

VPS/VDS is the sweet spot. Minimum configuration for a medium online store: 2 vCPU, 4 GB RAM, SSD. Optimal: 4 vCPU, 8 GB RAM. OS: Ubuntu 22.04 or Debian 12. If not BitrixVM, we configure the stack manually for the task. Virtual hosting — only for business cards and landing pages. Requirements: PHP 8.0+, MySQL 5.7+ / MariaDB 10.0+, 512 MB RAM, .htaccess. 1C-Bitrix hosting partners guarantee compatibility. Dedicated server — for highload. Typical architecture: web server separate, database separate, Redis/Memcached separate. For Enterprise edition — web cluster with load balancer. Cloud (Yandex Cloud, VK Cloud, Selectel) — when load spikes: sales, seasonal peaks. Autoscaling via Managed Kubernetes or simple VM vertical scaling.

Choosing the edition is equally important. A common mistake: choosing "Small Business" for a store that grows to B2B with wholesale prices and three warehouses in six months. Upgrading to "Business" — pay the difference, data is not lost, but it's better to plan ahead. Our specialists select the edition for current tasks and with room for growth. For example, the "Business" license (about 35,000 RUB) pays off through multi-warehouse and 1C exchange, while the wrong choice can lead to a loss of up to 30,000 RUB monthly on excess resources.

What Server Settings Are Critical for 1C-Bitrix?

Web Server and PHP

nginx as reverse proxy + Apache (mod_php) or nginx + PHP-FPM directly. The second option saves memory — Apache is not needed. But some Bitrix modules use .htaccess, so for compatibility we sometimes keep Apache. nginx configuration: fastcgi_read_timeout 300 — for long operations (1C import), client_max_body_size 1024m — large file uploads. Block access to .settings.php, .settings_extra.php, bitrix/.settings.php — they contain database passwords. Rewrite rules from urlrewrite.php — Bitrix generates them, but with nginx + PHP-FPM they need to be duplicated. PHP 8.0–8.2 with extensions: mbstring, curl, gd, xml, json, opcache, redis/memcached. Key php.ini settings: opcache.memory_consumption=256, opcache.max_accelerated_files=20000, max_execution_time=300, memory_limit=512M, upload_max_filesize=100M, post_max_size=128M.

Database and Caching

MySQL/MariaDB. Key my.cnf parameters: innodb_buffer_pool_size — 70–80% RAM, innodb_log_file_size=256M, tmp_table_size=256M, max_heap_table_size=256M, thread_pool_size — number of CPU cores. Encoding utf8mb4 mandatory, otherwise emoji and special characters break. Redis is preferable to Memcached for Bitrix — supports persistent connections and is more reliable. In production, Redis handles concurrent writes three times faster than Memcached under typical load. Configure in .settings_extra.php:

'cache' => ['value' => ['type' => ['class_name' => '\\Bitrix\\Main\\Data\\CacheEngineRedis']]]
'session' => ['value' => ['mode' => 'default', 'handlers' => ['general' => ['type' => 'redis']]]]

sudo apt install redis-server
sudo systemctl enable redis

SSL, Email, and Cron

SSL — Let's Encrypt via certbot in 90% of cases. Redirect HTTP → HTTPS (301), HSTS, TLS 1.2/1.3, OCSP Stapling. In Bitrix, switch to HTTPS in the main module settings. Email: abandon mail() — connect SMTP (Yandex.Mail for domain, Mail.ru for Business). Be sure to configure SPF, DKIM, DMARC. Without SPF, emails go to spam. Test deliverability via mail-tester.com — score 9+/10. Cron: Bitrix agents switch to system cron — * * * * * /usr/bin/php /var/www/bitrix/modules/main/tools/cron_events.php. Schedule 1C exchange (15–60 min), search reindex, backups (mysqldump + rsync, rotation 7+4), temporary file cleanup.

Security and Administration

File system: owner www-data, directories 755, files 644, upload 775. nginx blocks access to configuration files. Enable Bitrix Proactive Protection — WAF, activity control (block after 5 failed attempts), kernel integrity check. For admin panel: two-factor authentication via Google Authenticator or OTP, restrict access by IP via nginx for paranoid.

How Long Does 1C-Bitrix Installation and Configuration Take?

Post-Installation Checklist

1. Performance Monitor (/bitrix/admin/perfmon_panel.php) — aim for 30+ points. Below 20 means serious configuration issues.
2. System Check — automatic check of all parameters. Red items must be fixed, yellow — case by case.
3. Security Scanner — check for typical vulnerabilities.
4. PageSpeed Insights — TTFB < 200ms on VPS, LCP < 2.5s.
5. Test 1C exchange — if integration is planned, verify CommerceML exchange before launch.

Additionally, check software versions, caching settings, cron operation, SSL certificate, SPF/DKIM/DMARC, access rights, delete default users and pages. For projects with 54-FZ, ensure fiscalization is configured via OFD provider.

Deliverables

• Fully configured server for 1C-Bitrix with MySQL, PHP, nginx optimization.
• Installed and activated license of the required edition.
• SSL certificate, email settings, cron and backups.
• Documentation: all configuration parameters, access credentials, cron tasks.
• Content manager training: how to log into admin panel, add products, upload images.
• Post-installation support for 30 days — consultations on settings.

Why Trust Professionals with Installation?

Incorrect installation means lost time and money. We've seen projects where a store on "Start" couldn't handle 50 visitors because innodb_buffer_pool_size wasn't configured. After migrating to VPS with correct configuration, the site "flew". Incorrect configuration can cost 30,000 RUB monthly due to excessive resource consumption. You get a ready-made architecture that scales. Order turnkey 1C-Bitrix installation — get a reliable platform for business growth. Contact us for a free consultation: we'll calculate the cost and time for your project. Over 7 years of experience, 120+ Bitrix projects implemented, including highload stores with million-item catalogs. Get in touch — we'll help configure Bitrix for your project.