PHP: What's the fastest way to query MySQL? Because PDO is painfully slow

Question

I need to perform a simply query.

Literally, all I need to perform is:

SELECT price, sqft, zipcode FROM homes WHERE home_id = X

When I use PHP PDO, which I've read is the recommended way to connect to a MySQL database, simply creating the connection takes a measured 610ms.

My code is below:

try {
    $conn_str = DB . ':host=' . DB_HOST . ';dbname=' . DB_NAME;
    $dbh = new PDO($conn_str, DB_USERNAME, DB_PASSWORD);
    $params = array();    
    $sql = 'SELECT price, sqft, zipcode FROM homes WHERE home_id = :home_id';
    $params[':home_id'] = X;
    $stmt = $dbh->prepare($sql);    
    $stmt->execute($params);
    $result_set = $stmt->fetchAll(PDO::FETCH_ASSOC);
    // json output  
    ob_start("ob_gzhandler");
    header('Content-type: text/javascript; charset=utf-8');
    print "{'homes' : ";
    print json_encode( $result_set );
    print '}';
    ob_end_flush();
    $dbh = null;
} catch (PDOException $e) {
    die('Unable to connect');
}

Question: What's the fastest way for me to connect to my MySQL database to perform the query above?

Answer 1

If the slowness is due to having to reach over the network for each connection, and mysql having to do a reverse DNS lookup to check through its GRANTs table, then that overhead could very well account for a large chunk of the latency. Switching to persistent connections would make it a one-time cost for the life of the connection.

However, this does lead to othe problems. Since transactions are rolled back and locks released when the connection holding them is closed, going persitent means they'll stay active. Without taking great care in your code to not leave the connection in an inconsistent state, you could very well create a deadlock or at least lock out all other connections until you go in manually and clean up.

Answer 2

Fastest possible :

mysqli_connect("servername", "user", "pass") or die("can't connect");
mysqli_select_db("dbname") or die("can't select database");

list($price, $sqft, $zipcode) = mysqli_fetch_array(mysqli_query("SELECT price, sqft, zipcode FROM homes WHERE home_id = ".mysqli_real_escape_string($home_id)));

[EDIT]: Now using mysqli instead of mysql.

Answer 3

Guess PDO is as fast as MYSQLI. I think your problem is how you connect with PDO. Propably your connectionstring looks like:

:host=localhost;:dbname=foo

And there is the problem... PDO tries to connect to localhost but PDO uses the DNS to turn localhost into 127.0.0.1 and this is what costs time. If you use 127.0.0.1 directly you wont have these problems anymore :) So the connectionstring must look like

:host=127.0.0.1;:dbname=bar

Answer 4

as of version php 5.3.0 the fastest and most lightweight way of calling into the db from php is as follows:

This example uses the mysql/ext (not mysqli) and calls stored procedures

$conn = mysql_connect("localhost", "user", "pass");
mysql_select_db("db");

$sql = sprintf("call get_user(%d)", 1);

$result = mysql_query($sql);

mysql_free_result($result);
mysql_close($conn);

The stored procedure:

delimiter #
create procedure get_user
(
in p_user_id int unsigned
)
begin
    select 
     u.user_id, u.username, u.status_id, s.name as status_name, ...
    from 
        users u
    inner join user_status s on u.status_id = s.status_id
    ...
    where 
      u.user_id = p_user_id;
end #

delimiter ;