MySQL Partitioning for 1C-Bitrix: Speed Up Queries 10-50x

MySQL table partitioning for 1C-Bitrix is a technique that solves the problem of the b_stat_hit table growing to 50 million rows, where DELETE of old records locks the table for minutes and SELECT by date range goes through a full scan. This is typical for projects without partitioning. Our engineers with 10+ years of experience, Bitrix certification, and over 50 successful projects guarantee correct implementation without downtime. Partitioning splits a MySQL table into physical segments. Queries only hit the required partition, and deleting old data is an instant DROP PARTITION instead of a heavy DELETE. In one project with 80 million statistics hits, data deletion time for a month dropped from 12 minutes to 0.1 seconds — a 7200x improvement. In another project with a catalog of 500,000 products and 100,000 daily hosts, b_stat_hit grew by 3 million rows per day — after partitioning, lock issues disappeared.

Partitioning is not a silver bullet, but for large tables with a temporal dimension it gives a 10-50x performance boost on selection and cleanup operations. We use RANGE partitioning by date — the most transparent and maintainable option for Bitrix. Below, we break down which tables to partition first and how to implement it without downtime.

Which Bitrix Tables Should Be Partitioned?

Not all tables benefit from partitioning. Candidates are large tables with a temporal dimension:

Statistics tables are the primary candidate: data older than 3 months is rarely needed, and DELETE FROM b_stat_hit WHERE DATE_HIT < NOW() - INTERVAL 3 MONTH on 30 million rows takes 10 minutes of locking. After partitioning, deletion takes milliseconds.

Why Is This Important for Bitrix Performance?

Without partitioning, MySQL scans the entire table even if only last week's data is needed. With partitions, the optimizer performs partition pruning — it skips partitions that don't match the WHERE clause. This reduces I/O and CPU load by tens of times. For example, a SELECT with a date filter on a partitioned table runs 30-50x faster. Here's a side-by-side comparison:

Such acceleration directly impacts report speed and log cleanup. Contact us for a database analysis and effect estimate.

How to Automate Partition Rotation?

After setting up partitions, you need to regularly add and remove them. We use a cron script in Bash that runs monthly. Example logic:

#!/bin/bash MONTH=$(date +%Y-%m) PART_NAME="p_$MONTH" SQL="ALTER TABLE b_stat_hit REORGANIZE PARTITION p_future INTO (PARTITION $PART_NAME VALUES LESS THAN (TO_DAYS('$(date +%Y-%m-%d -d '+1 month'))'), PARTITION p_future VALUES LESS THAN MAXVALUE);" mysql -u user -p db -e "$SQL" # Delete old partition, e.g., older than 6 months OLD_PART=$(date +%Y-%m -d '-6 months') OLD_SQL="ALTER TABLE b_stat_hit DROP PARTITION p_$OLD_PART;" mysql -u user -p db -e "$OLD_SQL" 

The script must have table-alter privileges. We configure it on the server and test automatic rotation.

How We Implement Partitioning: Step by Step

1. Analyze candidate tables: size, query patterns, growth speed.
2. Modify primary keys if needed (add date column).
3. Create RANGE partitions by date. Example for b_stat_hit:

ALTER TABLE b_stat_hit PARTITION BY RANGE (TO_DAYS(DATE_HIT)) ( PARTITION p_jan VALUES LESS THAN (TO_DAYS('2099-02-01')), PARTITION p_feb VALUES LESS THAN (TO_DAYS('2099-03-01')), PARTITION p_mar VALUES LESS THAN (TO_DAYS('2099-04-01')), PARTITION p_future VALUES LESS THAN MAXVALUE ); 

Documentation MySQL Partitioning requires that the partitioning column be part of every unique index and primary key. For b_stat_hit, the primary key is ID. We change it to a composite key:

ALTER TABLE b_stat_hit DROP PRIMARY KEY, ADD PRIMARY KEY (ID, DATE_HIT); ALTER TABLE b_stat_hit PARTITION BY RANGE (TO_DAYS(DATE_HIT)) (...); 

We verify that Bitrix does not use ID for JOINs — if it does, the composite PK is safe.

4. Set up a cron script for partition rotation:

   • Create new partition: REORGANIZE PARTITION p_future INTO (PARTITION p_apr VALUES LESS THAN (TO_DAYS('2099-05-01')), PARTITION p_future VALUES LESS THAN MAXVALUE).
   • Drop old: ALTER TABLE b_stat_hit DROP PARTITION p_old.

   Test performance: measure SELECT and DELETE times before and after. Typically improvement is 10-50x, and data deletion is thousands of times faster.

   What's Included in Our Work

   • Database analysis and candidate table identification.
   • Primary key modification without data loss.
   • Creation of RANGE partitions by date.
   • Development and setup of autoroation cron script.
   • Compatibility check with Bitrix kernel updates.
   • Documentation of all changes.
   • Post-implementation support (1 month).

   Request a consultation with a partitioning engineer. We will analyze your database and propose the optimal solution.

   Limitations in the Bitrix Context

   • Kernel updates. The main module may execute ALTER TABLE upon update — if the structure changes and partitions aren't accounted for, the update may break. We maintain a list of partitioned tables and check before each update.
   • ORM D7. Bitrix\Main\ORM is unaware of partitions — queries work transparently, but the MySQL optimizer only performs partition pruning if the WHERE clause includes the partitioning column.
   • InnoDB limits. Maximum 8192 partitions per table (MySQL 8). For monthly partitioning, that's over 680 years.

   MySQL table partitioning for 1C-Bitrix is a proven way to boost performance, especially on high-load projects. Get a free consultation from a partitioning engineer. Request a performance analysis today.