Database

• Check your db index usage... mysql's EXPLAIN is your friend.
• Between LOCK TABLES & UNLOCK TABLES, never call a subroutine.
• Check the DB error code after an SQL statement. Just because it worked once and the SQL is correct, that doesn't mean the table might not go corrupt, a disk might fill up, or index space within the file might not fill up. Errors happen. Deal with them.
• Preferred way to break up a long SQL query:

        $sth = $dbh->prepare("SELECT cola, colb, colc, cold FROM foo ".
                             "WHERE colb<>cola AND colc=22");
        

• Note on variable naming:

  $sth	statement handle
  $dbh	one database handle (usually the master)
  $dbs	set of database handles [master(, slave)]
  $dbr	read-only slave db handle (used for selects)
  $dbarg	argument that can take a $dbh/r or $dbs
  $remote	hashref of remote user, based on cookies. will contain 'userid' and 'user' params, unless faster get_remote_noauth was used, in which case only 'user' will be present.
  $u	a user 'object' (a hashref)

• Call for database handles as you need them, instead of predeclaring $dbx variables. Use LJ::get_db_writer to get a writable handle to the database, and LJ::get_db_reader to get a read-only handle.