Each database management system has a separated memory for sorting data.
When the data is large that may required more memory to sort, if the required memory exceed the mysql sort memory, mysql will save some data to disk. Because writing, reading data from disk is much slower than RAM the query speed is also slow down.
We have 2 solutions for this problem:
When the data is large that may required more memory to sort, if the required memory exceed the mysql sort memory, mysql will save some data to disk. Because writing, reading data from disk is much slower than RAM the query speed is also slow down.
We have 2 solutions for this problem:
- Increase the sort memory in mysql setting
- Indexing so mysql can use that index for sort
1. Increase the sort memory in mysql setting
- We can config sort_buffer_size in mysql to increase sort memory. By default mysql sort_buffer_size is 262144 bytes.
- Sort memory will be allocated each time mysql receives a order request. Mysql can allocate amount of memory <= sort_buffer_size depends on each query.
- Increase sort_buffer_size can speed up the query in case that query needs a large amount of memory but it also slow down other normal queries.
- Mysql suggestion is only increase sort_buffer_size after apply all other optimization methods.
- We should only change this setting for some specific sessions (that require a lot of memory for sort). We can use optimizer_hints to set variable for each query.
- sort_buffer_size can be set up to 4GB.
- Sort memory will be allocated each time mysql receives a order request. Mysql can allocate amount of memory <= sort_buffer_size depends on each query.
- Increase sort_buffer_size can speed up the query in case that query needs a large amount of memory but it also slow down other normal queries.
- Mysql suggestion is only increase sort_buffer_size after apply all other optimization methods.
- We should only change this setting for some specific sessions (that require a lot of memory for sort). We can use optimizer_hints to set variable for each query.
- sort_buffer_size can be set up to 4GB.
SET SESSION sort_buffer_size=value
2. In case mysql has to store sort data on disk, what exact location that data will be stored?
- The location of temp file changes according to operation system:
- Unix: /tmp, /var/tmp or /usr/tmp
- Windows: C:\windows\temp\
- The location can be changed by setting tmp_dir