Controlling optimizer performance
Writing by corra on Tuesday, 27 of November , 2007 at 3:09 pm
The optimizer is the part of the mysql server whose responsibility is to discover the optimal plan (the execution oath) to solve an SQL query.
When dealing with joins there exist various execution paths depending on the number of tables involved. The number of possible plans to investigates grows exponentially with the number of tables. This is not a problem for queries with few tables (less than 10) but could be a serious problem when the number of tables climbs.
When dealing with dozens of tables the time spent to evaluate all possible plans to solve the query is longer than the execution of the query itself. Not good.
Starting from version 5.0.1 MySQL introduces a flexible method to avoid the possible bottleneck when you have lot of tables joined in a query.
Using the system variable optimizer_prune_level (possible values are 0 or 1) you can tell the optimizer to skip certain plans from being inspected based on the evaluated number of rows involved for every table. The algorithm is heuristic. The default value is 1 (enabled) and this means that a limited number of execution plans are evaluated instead of all possibile combination. This leads to a significant increase of performance when dealing with multi-table join queries.
Setting optimizer_prune_level=0 leads to a much longer query compilation. This is not a problem with few tables but don’t use it if not.
Another useful system variable is optimizer_search_depth. It tells the optimizer how far in the future to expand an incomplete plan. Also in this case the optimizer is able to compile the query faster. As the value of this variable is close to the number of tables involved in a join, the compile time is much longer.
Setting this variable to zero means that the server will determine tha optimal value for every query.
As in the previous case, this variable has negligible effect with few table joins and usually the value set to zero is the best solution.
Surely, using these system variables leads to better performance, but could be possibile that the optimizer fails to find to optimal solution. If you think that the optimizer is failing you can help it forcing or avoiding the use of certain index (FORCE INDEX, IGNORE INDEX) or maybe forcing an order to read the tables (STRAIGHT_JOIN). This are issues left to you and the knowledge of your tables and indexes.
Leave a comment
Category: MySQLen
No comments yet.
