When I run the following:

show processlist

I've been getting some entires of: Copying to tmp table

They don't hang like most of the people have when I do a search in google for the problem. What I want to know is, what does this mean? I just started noticing this and the load on the server seems to be higher than normal.

Thank you

In order to process a query, MySQL is copying to a temporary table in memory. The server will do this often and it is normal. However, if your database or code is not optimized, it can cause longer query times and higher load on the server.

If you have access to the MySQL client program or phpmyadmin, do an

EXPLAIN [your query] to see some information on it.

I ran the explain and it appears that it only does this when I use either group by or order by on the table. How would I optimize this better? I do have indexes on the fields being used in both the group by and order by for the table in question.

It's "normal" for tmp tables to be created on group by and order by. How long is the query running? Is it causing performance issues?

It's running for about 2 seconds. I know that doesn't seem very long, but it is for a left hand nav menu. This menu has online products which causes a complex system of what products a user has and what products should trigger other products. During peak times, this can cause some slowing on the site. The left hand menu only gets created when the user logs in.

It's hard to know what is going on without the table structure and query.

It sounds like a table spool. Is it eager or lazy?

Originally Posted by flann

It's running for about 2 seconds. I know that doesn't seem very long, but it is for a left hand nav menu. This menu has online products which causes a complex system of what products a user has and what products should trigger other products. During peak times, this can cause some slowing on the site. The left hand menu only gets created when the user logs in.

Why are you running this query every time a user navigates? Run it once, and cache the results. Invalidate them from time to time if you have to, but you should get the data from your database server to your web server once, and then use it from there. That's the only way to have a scalable system, whether the query takes 2 hours or 2 milliseconds to run.