We often get asked to help when a magento site has a problem and the overnight tasks take a long time to complete. Above the obvious checks, we also check MySql configs, webserver configs (we prefer to use Nginx), caching engines and hosting environments (many of which we find have often been left at server install default values and can be adjusted to get better performance).

As most people are aware, the speed of your site will be one of the biggest turn offs for visitors and also relevant for search rankings in the major search engines' complex algorithms for positioning. 

We often see clients who have lots of products, large numbers of categories and multiple sites who see a slow down of reindexing, which sometimes run for in excess of 8 hours. Whilst this reindexing is running, some of the sites are not even accessible. Although you might not have customers accessing your store at 4am, you might have the search engines crawling your site and measuring site speed, so it's important that they don't encounter the slow down.

With many years of Magento experience, we know where to look and what to change to get the most out of your hosting environment, and were surprised to recently became aware of a MySql setting which has a dramatic effect on the speed in which the overnight reindexing completes. Our surprise is that it has not been published in general Magento forums.

The optimizer_search_depth variable tells how far into the “future” of each incomplete plan the optimizer should look to evaluate whether it should be expanded further. This variable can be set to 0 to tell the optimizer to determine the value automatically.

We have seen reindexing which used to take 8 minutes to complete drop to less than 30 seconds. Please share this golden gem as we believe that it should be public.

An article explaining the setting https://www.percona.com/blog/2012/04/20/joining-many-tables-in-mysql-opt....