My intention here was to just introduce the OPTIMIZE FOR hint.Each storage engine is responsible for the relevant shutdown operation, for example, MyISAM will refresh all operations waiting to be written InnoDB will flush the Buffer pool to disk (from MySQL 5.0.5)ġ. You are most welcome to put in your comments. Many of you who are reading this now will love to talk more about other solutions and techniques but as you know the concept gets bigger and more complicated when we look into other hints like RECOMPILE, re-usage of plans, plan caching & recompilation, etc. This value is used only during query optimization, and not during query execution.Īdvantage: you get optimized plan for most of your queries.ĭownside: some queries that could benefit from the hash loop plan, will have to live with the nested loop plan. OPTIMIZE FOR hint now instructs the query optimizer to use 288 for when the query is compiled and optimized. Now, whatever be your sales person id, you will always get a nested loop plan. WHERE SalesPersonID= (OPTIMIZE FOR = 288)) So, this is how I construct the query: DECLARE int And it might make business sense that there will be few sales persons who would be star performers and will produce records more than 224 majority will have much lesser sales ? (I don’t deny it could be otherwise) (I am using the term ‘nested loop plan’ and ‘hash plan’ just to differentiate between the two plans). Now, if you know (from the past usage of the table) that most of the times users punch in a SalesPersonID that produces a Nested Loop plan, you can use OPTIMIZE FOR hint. Both the above queries produce different actual number of rows, but the same estimated number of rows and that is because of default selectivity. You can see this value when you compare estimated number of rows with actual number of rows. In my case, the default selectivity was 223.882 and thus a hash match was a better plan for this value. Simply because now the optimizer does not get the variable value until runtime and uses default selectivity. WHERE SalesPersonID= will observe that both the above queries produce the same hash match plan. Now run the same queries again, but this time using a variable and observe the execution plan. Thus, the optimizer sniffs the values, uses statistics, computes the correct cardinality estimation and produces an optimized plan, which in our case is different.
![mysql optimizer settings mysql optimizer settings](https://cdn.crunchify.com/wp-content/uploads/2021/04/How-to-Optimize-WordPress-Database-All-tables-All-in-One-Optimizer-and-Customizer-aiooc-1536x498.png)
The difference between the above two queries is just the constant value.
![mysql optimizer settings mysql optimizer settings](https://sslprod.oss-cn-shanghai.aliyuncs.com/stable/slides/Optimizing_MySQL_Configuration/Optimizing_MySQL_Configuration_1440-07.jpg)
Note that this query produces 473 records. When you run the following code, you will see in the execution plan, Seek, idx_date scan & hash match. Note that the parameter value 288 produces only 16 rows.
![mysql optimizer settings mysql optimizer settings](https://insmac.org/uploads/posts/2017-09/1505990994_mysql-optimizer_02.jpg)
Mysql optimizer settings code#
When you run the above code snippet with parameter value 288 (turn on actual execution plan), you will observe Index Seek, bookmark lookup and nested loop join.
![mysql optimizer settings mysql optimizer settings](https://i0.wp.com/www.betterhostreview.com/wp-content/uploads/optimize-mysql-database-tables-phpmyadmin.jpg)
There is something called parameter sniffing – the query optimizer sniffs the values/constants specified in your query predicates and produces and optimized query plan based on the sniffed value. I am so sleepy, yet blogging on query hint if I make mistakes, pardon me ? I really do not know what motivates me or drives me to write this blog post at 12 midnight, just landed from Singapore and waiting for my BA flight to Redmond – I guess it you, the readers and audience of that motivates each author who is blogging here – when they see thousands of hits and comments, that really motivates :).