Today I had a sql query joining a bunch of tables that looked to be hanging. I ran it and went to lunch, and when I got back it still wasn’t done. So I killed all my connection threads, restarted the server, tried to find every way I could to kill any errant table locks that were hanging around, but nothing would make it complete. Finally after going through the syntax again I noticed that I left off the condition joining one of the tables, so it was joining 500,000 rows * 100,000 rows and it just hadn’t finished in the hour I left it to work.
I was more likely to have this problem because of the joining syntax I was taught:
from table1, table2
where table1.id = table2.id
As you add more tables the where clause gets longer, and combined with the rest of the query’s constraints it gets easy to lose track of the ones that are necessary to do the joins properly. My boss never has this problem because he uses a different syntax:
inner join table2 on table1.id = table2.id
This way, you can easily see what constraints are joining the tables together and would know if there was something missing. Also, this syntax is more like what you would use for left and right joins, so your queries will be more consistent. So from now on I’m going to use this syntax to try and avoid wasting time figuring out a problem that’s so easily preventable.