Queries from [sf_object_data] table are doing full table scan instead of using the available indexes.
 

Sample query:
SELECT a.`id` AS COL1, e.`parent_prop_id` AS COL2, e.`id` AS COL3, e.`voa_class` AS COL4, e.`app_name` AS COL5, e.`collection_index` AS COL6, e.`dictionary_key` AS COL7, e.`is_backend_object` AS COL8, e.`last_modified` AS COL9, e.`multilingual_version` AS COL10, e.`object_type` AS COL11, e.`parent_id` AS COL12, e.`parent_prop_id` AS COL13, e.`strategy` AS COL14, e.`vrsn` AS COL15, e.`voa_version` AS COL16, e.`allow_security` AS COL17, e.`base_control_id` AS COL18, e.`can_inherit_permissions` AS COL19, e.`caption_` AS COL20, e.`id2` AS COL21, e.`description_` AS COL22, e.`editable` AS COL23, e.`inherits_permissions` AS COL24, e.`is_data_source` AS COL25, e.`is_layout_control` AS COL26, e.`is_overrided_control` AS COL27, e.`is_personalized` AS COL28, e.`original_control_id` AS COL29, e.`ownr` AS COL30, e.`personalization_master_id` AS COL31, e.`personalization_segment_id` AS COL32, e.`place_holder` AS COL33, e.`shred` AS COL34, e.`sibling_id` AS COL35, e.`page_id` AS COL36, e.`page_id` AS COL37, e.`enable_override_for_control` AS COL38, e.`page_id` AS COL39, e.`enable_override_for_control2` AS COL40, e.`page_id` AS COL41
FROM `sf_object_data` a
INNER JOIN `sf_draft_pages` AS b ON (a.`page_id` = b.`id`)
LEFT JOIN `sf_control_properties` AS c ON (a.`id` = c.`control_id`)
LEFT JOIN `sf_control_properties` AS d ON (c.`id` = d.`prnt_prop_id`) 
LEFT JOIN `sf_object_data` AS e ON (d.`id` = e.`parent_prop_id`) 
WHERE a.`app_name` = '//'           
AND b.`id` = 'f54d1b40-c798-44e7-8895-ad9c43825f3e'                   
AND a.`voa_class` = 597140269
ORDER BY a.`id`, e.`parent_prop_id`

 

 

Problem specific to running Sitefinity on MySQL 5.7.12 and 5.7.31 versions.

Non-Sitefinity problem.
Sitefinity ships table indexes for all columns that are part of the query. They are to speedup the query instead of requiring full table scan.

The component that is responsible to make the query benefit from the available table indexes that prevent the full table scan is the MySQL optimizer.
There is such optimizer for every database server and its not provided and shipped by Sitefinity.
The one for MySQL 5.7.12 and 5.7.31 don`t do a good job in including the available index that will speedup the query operation.
There is a table index that solves the problem, but the optimizer determines it will not use it.

In this situation only optimization options are available:

Optimization 1:
The speed of executing a full table scan will depend on the hardware power that the MySQL server has. It is wort testing if a more powerful machine will process the full table scan faster.

Optimization 2:
Increase the Sitefinity database connection timeout to more than 30 seconds (by default is 30 seconds) so it can fit into the time to do the full table scan. Details on the code to add to the site is available Increase OpenAccess connection timeout.

Move the database to Microsoft SQL Server. Its optimizer makes use of all available indexes and doesn`t have this problem.