Every specific situation requires individually created
indexesSearch 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 is better to be performed after retrieving list of slow queries. Enables the
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.
- 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.
- 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.
The Existing indexes page (Settings > Performance > Indexes > Existing indexes) shows results for your analysis-related decisions on specific queries. "Green" status - index is created, "red" status - index won't be created.