A Day Late

I learned the benefit of using the “straight_join” keyword while working on a complex query at work today. For some reason the query would only complete if I was running it on the mysql client on the mysql server. All remote connections would simply die. Actually they didn’t die, they would hang. I watched the processlist as the query was running. The status indicated “copying to tmp table”. Eventually the query would disappear from the processlist but no results or information would return and the client appeared to still be waiting for a response. I tried increasing the tmp_table_size variable but that didn’t help. I suspect it has something to do with the tmp directory (possibly not enough space available or something like that).

Anyway, I was able to work around the problem by rewriting the query using the straight_join keyword. Apparently MySQL isn’t necessarilly good at choosing the join order in complex queries. By specifying a complex query as a straight_join the query executes the joins in the order they’re specified. By placing the table I assumed to be the least common denominator first and specifying straight_join I was able to improve the query performance by a few minutes. The new query also completed successfully on the remote clients. Now…if I could just figure out why the first query hung.

This entry was posted on Thursday, May 20th, 2004 at 11:55 pm and is filed under General, Programming, Sys Admin, Work. You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.