Bitrix24 Self-hosted administration

  Indexes

Every specific situation requires individually created indexes Search index - data structure, containing document-related information and used during site search. . Indexes are required for specific data retrieval for specific projects. Depending on the project logic and architecture, slow queries are unique and require custom indexes, often composite ones.

Analyze indexes and Existing indexes pages are dedicated to the tools for analysis and recommendations on creating indexes.

  Index analysis

Index analysis is better to be performed after retrieving list of slow queries. Enables the corresponding option in the module settings and set threshold time after a query is deemed as slow. Monitor operation - 24 hours. However, such parameters depend on the project.

After getting the list of slow queries at the Analyze indexes page (Settings > Performance > Indexes > Analyze indexes) use the Analyze logged SQL queries button and show list of all queries performed for a indicated period, sorted by table name:

Large amount of queries with longest duration require more attention in the general list. In case these parameters have large values, it's not recommended to create individual indexes for each query (possibly only modifying a component code is enough). Implicit criteria for successfully created index is query execution time before and after index has been created.

When required, you can overview execution plan for any query. Detailed analysis command allows analyzing specific query and creating its index.

This page highlights queried table and columns in bold.

Table structure - is informational tab. The main content is table size. And even if size is large (for example, 100 megabytes), deleting and building indexes is preferred for lowest site load hours.

Analyze queries - tab with query analysis. When deciding to create an index, consider the query selectivity and selectivity percentage. Information is printed in the table.

Create index - tab with option to create an index. The queries that doesn't require indexes can be logged to the Don't ask to create list.

Queries, are dismissed from query list and appear at the Existing queries.