Overview

When using MySQL databases, you can optimize performance by tuning various system variables. One important variable that can have a significant impact on optimizing query performance is the range_optimizer_max_mem_size setting. In this article, we’ll learn more about what range_optimizer_max_mem_size is and when to adjust it.

What is range_optimizer_max_mem_size?

The range_optimizer_max_mem_size specifies the maximum amount of memory, in bytes, that the MySQL server can use when performing range optimization during query execution. Range optimization is a technique used to more efficiently process range condition queries in WHERE clauses.

The default value can vary depending on the server version, but is typically set to 8 MB (8388608 bytes). This value limits the amount of memory required to optimize how the index is used to filter data when determining the query execution plan.

What is range optimization?

  • Range search: A search or lookup that targets values in a specific range. For example, in a query condition like WHERE price BETWEEN 100 AND 200, you are looking for rows where the value of the price column is between 100 and 200.
  • Temporary data structures: During optimization, MySQL can store values that satisfy the conditions of a range search in an in-memory temporary data structure. This allows for quick access and retrieval. The range_optimizer_max_mem_size is the value for the maximum size of this temporary data that will eventually go into memory; if it exceeds that memory value, it will use disk.

How to check the value

You can check the current value of the range_optimizer_max_mem_size setting by running the following SQL statement in the MySQL console or client.

1SHOW VARIABLES LIKE 'range_optimizer_max_mem_size';
2
3+------------------------------+---------+
4| Variable_name                | Value   |
5+------------------------------+---------+
6| range_optimizer_max_mem_size | 8388608 |
7+------------------------------+---------+

How to adjust

Here’s how to adjust the range_optimizer_max_mem_size value on your MySQL server.

1SET GLOBAL range_optimizer_max_mem_size = 16777216; -- 16MB

When to adjust

  • Poor performance: When running complex queries that deal with large data sets, a range_optimizer_max_mem_size setting that is too low can cause the MySQL server to use disk-based temporary tables. This can lead to poor performance.
  • Optimize resource usage: On the other hand, setting this value too high can put a strain on the server and unnecessarily consume memory that could be used for other important tasks. Therefore, you should set an appropriate value based on the memory capacity of your system and the current load.

Conclusion

The range_optimizer_max_mem_size setting plays an important role in optimizing the query performance of MySQL databases. With proper settings, you can optimize resource usage and reduce query response times. However, you should consider the overall resource usage of your system when adjusting this value, and be sure to test to determine the impact before making any changes.