Why copying to tmp table

Sign up or log in Sign up using Google. Sign up using Facebook. Sign up using Email and Password. Post as a guest Name. Email Required, but never shown. The Overflow Blog. Does ES6 make JavaScript frameworks obsolete? Podcast Do polyglots have an edge when it comes to mastering programming Featured on Meta.

Now live: A fully responsive profile. In this case tmpfs will not help as nothing is written to disk. In this 'show processlist' will show "Copying to tmp table on disk". But it doesn't look that this is your problem I don't see it in variables. You may be able to get that number smaller by increasing the size of in memory tmp tables if you have the memory.

Note however that this is memory given for every running query that uses tmp table. Is it not a real query? The innodb cache, the file system OS cache, the query cache?

It always happens when you do a complex order by on a table which is not the first one in explain. If the product of these gets big, then you have a problem. Questions: What would give the most performance increase of the above variables, or: will it be more useful to increase the total server buffer limit or the per thread buffer limits?

There are two suggested fixes that seem to get mentioned more that others: — Kill process. Do flush table.

Question: Which of the two above suggested solution do you think is the most viable? If you really don't want to touch the query which is a mistake - then your other option would be to create a RAM disk tmpfs for your tmp files. You can find out what that is set to by:. Look for the tmpdir variable. That will tell you where the tmp files are being stored. Sign up to join this community.

The best answers are voted up and rise to the top. Stack Overflow for Teams — Collaborate and share knowledge with a private group. Create a free Team What is Teams? Learn more. Asked 12 years, 3 months ago. Active 7 years, 5 months ago. Viewed 22k times. But I can't figure out where it's storing them. Is it an in-memory "file"? I'm running MySQL 5.