Issue DescriptionDue to a report performance issue, we have to execute a SQL SET optimizer_search_depth = 0; before executing the report main SQL. Setting this parameter globally in MySQL has negative impact for other processes that are running queries over the database. Is there a way to specify the parameter value at the connection level? | [toc] |
Resolution
According to MySQL doc, optimizer_search_depth variable can be set at both Global and Session level:
https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_optimizer_search_depth
MySQL JDBC connection URL has an option to include session variable:
https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-configuration-properties.html
------------
sessionVariables
A comma or semicolon separated list of name=value pairs to be sent as SET [sESSION] ... to the server when the driver connects.
-----------
So, if you use a JNDI datasource, you can modify it's definition in context.xml file (or another config file based on your deployment) and add a directive like this
&sessionVariables=optimizer_search_depth = 0
to URL
The complette URL should look like
url="jdbc:mysql://127.0.0.1:3306/<database_name>?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&autoReconnectForPools=true&sessionVariables=optimizer_search_depth = 0"
Ref. Case 01518302
Recommended Comments
There are no comments to display.