These tips will work for most SQL based server, including but not limited to PostgreSQL and MySQL.

1. Don’t use the DELETE statement

Instead, use an UPDATE statement to mark a row to be deleted at a later time. Build your database so that the tables have a ‘deleted’ column. Then, run the DELETE at a later time on all rows marked to be deleted. Just use a small boolean value in the ‘deleted’ column, or you could even call it ’status’ and leave room for more status’s of a row by using an integer or small character value.

This can be big. Put simply, an UPDATE statement is cheeper than a DELETE statement because your DELETE statement will force the entire table to re-index the primary keys and the indexed.

2. Reduce IO’s by reducing SELECT calls

If you use UPDATES a certain way, and your database is also using UNIQUE fields correctly, you can reduce SELECT statements.

For example, if you have a table where you want to decide wether to INSERT or UPDATE, you might try running a SELECT to check for the data first. That will ALWAYS result in two calls.

<?php
// this code will always result in two calls !!
$count = functRunQuery("SELECT COUNT(*) FROM user_table WHERE username='john';");
if ($count > 0) // then run UPDATE
else // run INSERT
?>

Instead, run the UPDATE or INSERT first without using a SELECT call if you can…

<?php
// this code could result in one call
$affectedRowCount = functRunQuery("UPDATE user_table SET username='bob' WHERE username='john';");
if ($affectedRowCount > 0) // we're done, don't need to do anything
else // didn't find any rows so run the INSERT here
?>

OR, if your table would probably see more successful INSERTs than UPDATEs do it the other way around (PHP5 method here)…

<?php
// this code could result in one call
try {
// try inserting the user without checking possible unique conflicts...
functRunQuery("INSERT INTO user_table (a_unique_username) VALUES ('bob')");
} catch {
// catch the error here if 'bob' is already in the database, and run an UPDATE instead
}
?>

If scalability is a possible issue for your application, you should always be looking for ways to reduce database calls and cut down on re-indexing.