Bitrix Framework

Programmers who start using Bitrix Framework often have difficulties understanding database queries. Bitrix Framework code often results in very long queries that are not immediately understandable. Several windows of quite unclear SQL normally scare people who rarely write queries more complex than select * from ... where id=... and who are sure that merging tables by conditions through where is equally efficient as through on.

But not everything is so easy. Working with database involves the following aspects:

• Very specific process-oriented issues, like throughput rate of certain data selections;
• Issues of convenient application of database working techniques in large evolutionary projects based on expanding CMS;
• Issues of ownership and support cost.

Why is a Bitrix Framework query so hard to read?

The system has infoblocks where data structure is created quickly and conveniently. There are standard components that implement the most common things. There is API that permits sending quite arbitrary queries. You work with all this, and your fairly high level logic transforms into a query. Query generator, regardless of its complexity, performs quite monotonous task of translating logic written on a high level, into queries to specific fields of specific tables by conditions.

High capacity DBMS have clever and very efficient query optimizers. These DBMS will not even permit you to perform bad queries. The vast majority of products installed on Bitrix Framework use MySQL in which the optimizer is rather weak. As a result, we have long queries, limited means to change them maintaining the logic, and a caching mechanism on top of it all.

Disadvantages of the Situation

Complex queries are very long and your options to change its structure are very limited. Even if you want to profile and adjust them, you are actually limited to the same rather poor means to query the change offered by Bitrix Framework. You will not be able to:

• Change the merge order;
• Set up specific conditions for selections;
• Create local caching temporary tables.

Advantages of the Situation

The advantages turn out to be more important than disadvantages:

• You do not have to write queries yourself. Creating a project in Bitrix Framework usually involves visual setup of ready-made components, creation of website structure, and design integration. You do not have to customize components for every project, but you do not have to write queries even for customizing.
• Security. Query wrappers protect from attacks or developer’s nonsense.

Developer’s Qualification

Like in any other environment, everything depends on professionalism of work with Bitrix Framework. In this case, proficiency in database operation is not crucial. In the majority of projects the workload on sql (execution time) is insignificant compared to the workload on the processor and the efforts spent on script interpretation. It is not MySQL but PHP that puts on the brakes.

That is why the correct design of data structure and selection and implementation of links through the system of infoblocks and tables are of crucial importance. Project “brakes” could be eliminated much more properly and efficiently using correct design rather than through the optimization of a query generated by Bitrix Framework following your instructions.

The use of cache permits to save time on query execution. Proper distribution of logic by components makes it possible working without even accessing them. There is a rule: the home page (generally not the most simple) must not send database queries when the cache is activated.

A developer must take into account the project specifics and select a proper table type: InnoDB or MyISAM .

РA developer must be able to evaluate the level of tasks. For really serious projects, there is My SQL clusterization.

Database Tools in Bitrix Framework

Before optimizing projects, use the Performance Monitor tool in order to find bottlenecks on the website.

The Database check and system optimization tools are available as a part of administrative part of the system. These actions are automated, and developer cannot interfere with them.

Use infoblocks 2.0. This is an infoblock operating mode that can be activated in the administrative section by a check mark. It makes infoblock’s fields transferred into a separate table. But they have their own specifics that must be learned and taken into account.

On standard MySQL mechanisms the use of database clusterization is recommended. It can be set up from the administrative part.

Use the indexes specific for your project. A long query does not always mean a long execution. Take a heavy query from the system and click explain on the console. You will be surprised to see how many indexes and how little round robins are there.

Working with Database

Before start working with a database, please keep in mind the following:

For this reason, the names of the tables are not disclosed. But if you assume the responsibility and decide to work directly with the database, take into account that all tables from Bitrix Framework start with b_. Accordingly, your prefixes must be different: it is mandatory that the names of your tables do not interfere with those of Bitrix. It is entirely possible that in new updates a table with the same name as a table created by you will appear (if you use the prefix b_). At best, the update will not be installed.

Use the methods of the global variable $DB (class CDatabase) to work with own tables.