How to create a faceted bike?

Question

SQL

Good afternoon,
in one of the projects I started to catch heavy requests in connection with the search in the catalog, which has more than 10,000 items and a lot of filtering options.
The project works on "Bitrix - Start" without a smart filter and faceted indexes.

Bitrix request rate testing conditions:
Filter: for CIBlockElement::GetList

$arFilter = array(
        'IBLOCK_ID'               => '8',
        'PROPERTY_district'       => $params['district'],
        '><PROPERTY_area'         => array($params['area_from'], $params['area_to']),
        '><PROPERTY_price_total'  => array($params['price_from'], $params['price_to']),
        'PROPERTY_deal'           => $params['deal'],
        'PROPERTY_rooms'          => $params['rooms'],
        '><PROPERTY_floor'        => array($params['floor_from'], $params['floor_to']),
        'PROPERTY_is_new'         => $params['building_new'] == '1' ? 'true' : 'false',
        '><PROPERTY_floors_total' => array($params['floors_from'], $params['floors_to']),
        'PROPERTY_city'           => $params['city'],
    );

Field selection: array('IBLOCK_ID', 'ID')

The tested query without cache returns the result in 3.49609804153 seconds.

Query speed test conditions for auxiliary table:
Query: same fields (columns) and values ​​except IBLOCK_ID

SELECT * FROM `filter_values` WHERE (`district` IN ('...')) OR (`area` BETWEEN '...' AND '...') OR `is_new` = 'false' OR 
...
// Столбцы id | element_id | district | area | is_new | ...

Field selection: *
Second request for CIBlockElement::GetList:

$arFilter = array(
        'IBLOCK_ID' => '8',
        'ID'    => $arIds,
    );

Field selection: array('IBLOCK_ID', 'ID')

By creating a separate table that contains the element ID and its properties in columns by which filtering can occur, we managed to get the element IDs in 0.107703924179 seconds.
Further, the received IDs are transferred to GetList, which completes in 0.911054849625 seconds.
Using your bike you can achieve the same result in 1 second.
Actually in what a question what problems or a rake can arise using such approach in the future? Probably there is a decision easier than to fence the auxiliary table?
Thank you.

Answer 1

Your decision is quite the place to be , and the rake will be the same as in Bitrix - rebuilding the cache .
For example, you have added a field, which means that you need to rebuild the cache (i.e. delete and rebuild), and on a working site it takes a decent amount of time.
In addition, to speed up, you will either have to exclude some fields or duplicate entries from multiple lists, but unloading the database can partially help with this.
Two thoughts come to mind:
- with mysql 5.7+, you can check how the json fields will behave (so as not to rebuild the schema each time, but simply rewrite the values)
- try to put this decision somewhere on mongodb (append fields there without rebuilding).
But in general, your option is quite suitable (except that the option with rebuilding the cache still needs to be worked out).