Ovais, Syntactic hints surely help pushing the performance in case of particular queries … doing a good job in the first place. In addition, hints need to be built into the (application’s) logic which…
Comment: Joining many tables in MySQL - optimizer_search_depth
April 25, 2012 By 5 Comments
Peter, I think even changing the query to use index/join hints can bring down the time its taking the query to complete, did you try with STRAIGHT_JOIN and/or index hints.
Comment: Joining many tables in MySQL - optimizer_search_depth
May 12, 2012 By 5 Comments
Bartosz, You are right this is due to an optimizer inefficiency. But till the time the optimizer is not efficient enough we would have to use workarounds. In fact hints tend to be used a lot because although mostly optimizer makes good choices but sometimes it doesn’t.
Comment: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 11, 2012 By 2 Comments
@Igor, Yes you are right the original query does not have an index hint, but I have had to make the QEP selected by the optimizer consistent otherwise it would keep flipping. I would post the numbers for the query without the index hint soon.
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
I have been working with Peter in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a look at and benchmarking optimizer enhancements one by one. So in the same way this blog post is aimed at a new optimizer enhancement Index Condition …
Comment: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 6, 2012 By 2 Comments
… remind you that the original query does not contain any hints: select l_orderkey, sum(l_extendedprice*(1-l_discount)) as…
Comment: The ultimate tool for generating optimal my.cnf files for MySQL
March 14, 2012 By 34 Comments
… to even say anything worth saying to give people a hint as to what kind of information his book might have…
Post: MySQL Session variables and Hints
December 28, 2006 By 2 Comments
… to find tune execution of particular query. First is MySQL Hints, such as SQL_BIG_RESULT, STRAIGHT_JOIN, FORCE INDEX etc…_size=DEFAULT after executing the query. I noticed in production hints are used much more frequently than setting session variables for…
Post: Guidance for MySQL Optimizer Developers
September 20, 2009 By 4 Comments
… to get query going right manually. Zero Administration Tunables and Hints Zero Administration is great. I would love to see Optimizer… to cost model and various optimizations) as well as simply hints. Any way MySQL could possibly execute query should be possible… benefit of having ways to force any optimizer behavior with hints (rather than by changing the code) is the data Optimizer…
Post: SELECT LOCK IN SHARE MODE and FOR UPDATE
August 6, 2006 By 51 Comments
… IN SHARE MODE and SELECT FOR UPDATE are more than hints. Behavior will be different from normal SELECT statements. Here is… be some differences but logic stills the same. So these hints are very powerful and helpful for application development but should… shared locks on Innodb table but no updates. Supporting MySQL hints “HIGH_PRIORITY” and “LOW_PRIORITY” would probably be good start…
