Is it created in the server / tmp folder? Is it possible to define another way to create a temporary table so that it is not destroyed in the / tmp folder? What is the speed of queries from the temporary table. The temporary table contains a million rows.

  • about the location and its changes in sufficient detail in the documentation . - aleksandr barakin

1 answer 1

Whenever possible MySQL tries to place a temporary table in RAM. The maximum size of such a table is determined by the system variable tmp_table_size value of which can be set in the my.cnf configuration file in the [mysqld] section (default size is 16Mb). Please note that the size is determined by the amount of memory, and not by the number of rows - you have to calculate whether the table is loaded or not. EXPLAIN , unfortunately, does not provide this information - if it claims that file sorting will be used, it can take place both in RAM and on disk, depending on whether the resulting table is removed in tmp_table_size or not.

 [mysqld] ... tmp_table_size = 128M

For temporary files, MySQL uses the system temporary folder. However, it can be changed using the tmpdir directive, which should also be placed in the my.cnf configuration file in the [mysqld] section

 [mysqld] ... tmpdir = /path/to/anoter/tmp tmp_table_size = 128M

After rebooting, the server will use the new temporary folder.

  • Thank you very much for the answer, yes, tmpdir was created, but you can ask about the quotation: "EXPLAIN, unfortunately, does not provide this information - if it claims that file sorting will be used" - is that how? - NNN
  • one
    @NNN Sometimes in the EXPLAIN report's additional field (you can get it by adding EXPLAIN before any SELECT query) displays “Using filesort” - this means that the intermediate result table will be unloaded on the hard disk and sorted on it, after which the results will be sent to the client. However, if the amount of RAM allocated in tmp_table_size allows, then instead of the hard disk, this operation will be performed in RAM. Therefore, "Using filesort" does not always mean an operation on a hard disk. - cheops 3:51 pm
  • thanks a lot !!!! -) - NNN