Avoiding MYSQL temporary tables

[mark_roach]

My site uses a shared MYSQL server and once a week, someone does something that nearly brings it to its knees. My site continues to run ok unless any of my queries need to use a temporary table to return their result.

I have managed to eliminate this from all but one of my queries by the use of indexes, however I am stuck on the following very simple query:

select distinct column_name from table;

Does anyone know of a trick that I can use to avoid the use of a temporary table.

[jatar_k]

that's a little bit difficult to answer with out a little more info.
Why do you have to select the column names?
What is it you are trying to do?

[Bolotomus]

The short answer to your question is, either your SQL statement stinks, or (more likely) you need to have more keys, possibly composite keys, on your tables.

I have the same problem myself. There are techniques to make sure that your queries have all the necessary keys available to them, so that temporary tables are not needed. The EXPLAIN command does wonders to help you debug these issues.

HOWEVER ... in my experience sometimes MySQL uses temporary tables anyhow. I have been trying to optimize a certain query for months now, and I'm starting to conclude that it can't be done. I hope I'm wrong, because I really like MySQL otherwise.