November 4, 2004
Warning: Geek-stuff in this one.
I learned something recently. When you're putting together a SQL statement using more than one table, and you don't tie the tables together, it causes problems. Let's say I'm building a statement and conditionally calling stuff in from another table. To simplify my code somewhat, I might do something like this:
$sql = 'SELECT a.* FROM TableA a, TableB b';
if ($something) $sql .= ' WHERE b.SomeID=a.SomeID AND b.SomeID=' . $someVar;
Now, to me that looks like if $something is false, that TableB doesn't get used at all and would be ignored.
That would be wrong. Apparently, in situations like that the database will match every row of TableA with every row of TableB, creating a huge number of duplicates -- if there are 10 rows in TableB each row from TableA will be used 10 times. With my small test database this wasn't a problem: 10 rows by 20 rows just uses 200 records, and MySQL can handle that easily.
The production database, however, has a lot more stuff in it. So much that MySQL couldn't assemble the data before the connection timed out. Took me a while to figure that out. But I did, and now everything's working just fine.
I'm sure that's in the documentation somewhere, or maybe even in one of the SQL specs, but it's not like anybody ever reads those things. RTFM makes things easy, after all.
