Documentation: optimizer_search_depth=0 improves performance

Created on 26 March 2024, about 1 year ago

Problem/Motivation

I think documentation should be added to say that the following could greatly improve SQL query performance for people using MariaDB or MySQL.

Put in my.cnf on the DB server, or in .ddev/mysql/my.cnf

[server]
# Greatly improves performance of some SQL queries.
optimizer_search_depth = 0

This change cut Webform Views SQL query execution time from 38 seconds down to a few seconds for some of my queries.

Steps to reproduce

Create a view that would create SQL that has like 8 or more joins to the webform_submission_data table. Adding a bunch of fields will do it. Add a filter on one of the fields. Querying with optimizer_search_depth set to the default of 62 can be quite inefficient.

Proposed resolution

Add documentation

Remaining tasks

User interface changes

API changes

Data model changes

Feature request
Status

Active

Version

5.0

Component

Documentation

Created by

Live updates comments and jobs are added and updated live.
  • Performance

    It affects performance. It is often combined with the Needs profiling tag.

Sign in to follow issues

Comments & Activities

Production build 0.71.5 2024