MySQL Forums
Forum List  »  Optimizer & Parser

Re: Is it possible to avoid "Using temporary" when distinct is used with join?
Posted by: Rick James
Date: March 02, 2010 02:36PM

"Using temporary" quite possibly involves createing a temporary MEMORY table. The fallback if that cannot be done (certain MEMORY restrictions; limited memory, etc) is to create a MyISAM table, which might spill to disk.

Don't worry about "Using temporary"; it happens a lot of the time.

If you are worried about performance, then there are many other issues to watch out for. Having only 2-row tables now, it is premature to really test things. Turn on the slowlog, set long_query_time=2, get a few thousand rows, then come back for further critique.

BTW, how soon before you need something other than latin1?

I do worry that you are using consumer_opts as an open-ended key-value store. If you ever need to reach into that table for multiple key=value to find consumers, performance can plummet.

Later, you should increase the index cache:
key_buffer_size | 16777216
Recommend 20% of _available_ RAM.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Is it possible to avoid "Using temporary" when distinct is used with join?
1861
March 02, 2010 02:36PM


Sorry, you can't reply to this topic. It has been closed.
This forum is currently read only. You can not log in or make any changes. This is a temporary situation.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.