Re: Is it possible to avoid "Using temporary" when distinct is used with join?

[Rick James]

"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.