This site will look better in a browser that supports Web standards, but it is accessible to any browser or Internet device.

November 4, 2004

1993
2001
2002
2003
2004
January
February
March
April
May
June
July
August
September
October
November
Su M Tu W Th F Sa
  1 2 3 4 5 6
7 8 9 10 11 12 13
14 15 16 17 18 19 20
21 22 23 24 25 26 27
28 29 30        
December
2005
2006
2007
2008
2009
2010
2011
2012
2013
2014
2015
2016
2017
2018
2019
2020

Fun with SQL

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.

This page's URL is http://jasonfleshman.org

This page last updated Jul 19, 2019 3:34:19 PM.