1. For SELECT, SHOW, DESCRIBE, EXPLAIN and other statements returning resultset, mysql_query() returns a resource on success, or FALSE on error. For other types of SQL statements like INSERT, UPDATE, DELETE, DROP, etc, mysql_query() returns TRUE on success or FALSE on error (i.e. boolean result and not a resource). Use mysql_num_rows() to find out how many rows were returned for a SELECT statement or mysql_affected_rows() to find out how many rows were affected by a DELETE, INSERT, REPLACE, or UPDATE statement.

$sql = "UPDATE tablename SET id='123'";
$result = mysql_query($sql) or die("Error: Cannot execute SQL : ".mysql_error() );
if($result !==  false) // correct, context check
{
    $num_rows = mysql_num_rows($result);      //incorrect   rather use mysql_affected_rows()
    while ($row = mysql_fetch_assoc($result))   //incorrect for UPDATE statement
    { }
}

The returned result resource (by SELECT, SHOW, DESCRIBE, EXPLAIN, etc) should be passed to mysql_fetch_array(), and other functions for dealing with result tables, to access the returned data.

mysql_query() will also fail and return FALSE if the user does not have permission to access the table(s) referenced by the query.

A resource can be checked by the following,

$db_link = @mysql_connect('localhost', 'mysql_user', 'mysql_pass');
if (!is_resource($db_link))  {
   die('Can not connect : ' . mysql_error());
}

mysql_free_result() will free all memory occupied by the resultset.

mysql_free_result() only needs to be called if you are concerned about how much memory is being used for queries that return large result sets. All associated result memory is automatically freed at the end of the script’s execution. Using this function may actually increase the amount of memory used (use it when you know the resultset is really large). Use memory_get_usage() at the end of the script to see the memory usage by MySQL.

2. We sometimes pass the user inputs directly to the SQL statements which is a big security threat. Sanitize the user inputs before passing them to SQLs.

$query = sprintf( 'SELECT id, name FROM People WHERE name = IN (%s)',
implode(',', array_map( 'mysql_real_escape_string', $names_array )) );

function my_real_escape_string($string)
{
   if (get_magic_quotes_gpc())
      $string = stripslashes($string); //if magic quotes are enabled, it automatically prepends backslash
   return mysql_real_escape_string($string);
}

3. Number of rows affected is 0 (zero) if the same value is being updated using UPDATE in MySQL (actually it doesn’t update if the value is the same) hence creating a wrong impression that our update statement failed and giving an error. So proper check should be done before dumping an error.

Also, remember the parameter passed to mysql_affected_rows() is a link resource NOT a resultset.

While that to mysql_num_rows() is a resutset.

4. Instead of executing SQL commands in a loop try executing it once only and store the output in some variable or array. This increases efficiency and reduces execution time.

Wrong Approach,

$res = myql_query($first_query) or die ("Error 1");
while($row = mysql_fetch_row($res) )
{   //faster than mysql_fetch_assoc()
    $second_query = "select name from table where id= $row[0]";
    $res = myql_query($second_query) or die ("Error 2");
    while($row2 = mysql_fetch_row($res) )
    {
       ....
    }
}

Right Approach,

$str="";
$res = myql_query($first_query) or die ("Error 1");
while($row = mysql_fetch_row($res) )
{   //faster than mysql_fetch_assoc()
    $str .= $row[0].',';
}
//remove last comma
$str = (substr($str,-1) == ',') ? substr($str, 0, -1) : $str;

$second_query = "select name from table where id IN ($str)";
$res = myql_query($second_query) or die ("Error 2");
while($row2 = mysql_fetch_row($res) )
{
       ....
}

5. Always fetch definite columns from SQL and try to avoid * in select statements. It unnecessarily fetches all the columns which might not be needed.

SELECT * from table            //bad
SELECT id,name from table   //good

6. Open connections time out after a default setting (in MySQL conf file). But still, you should take the honor of closing the connections once you are done with it. If you don’t, PHP will close it on exit.

Useful SQLs to check performance:

a. Query “SHOW PROCESSLIST” shows which processes in MySQL are currently running.

b. Query “SHOW STATUS” shows the current number of connections.