8

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?

nickb
  • 9,140
  • 11
  • 39
  • 48
  • 1
    Do you have index set in home_id? you are connection thru localhost? Try to use mysql_* or mysqli_* (ie mysqli_connect, mysqli_query etc) – cichy Sep 04 '10 at 05:32
  • Yep, I have an index on home_id. My web server is on a different physical server than my MySQL database server – nickb Sep 04 '10 at 05:37
  • 1
    Thats the main reason of your problems. You could try caching your data in session/stored files. – cichy Sep 04 '10 at 05:45
  • 4
    @user434493: if your web server is on a different physical machine from your MySQL database, then network latency is a very likely culprit, even if they're sitting right next to each other in the same room. – BoltClock Sep 04 '10 at 05:46
  • Are you sure it is library problem, not particular server one? – Your Common Sense Sep 04 '10 at 05:49
  • +1 for a slow network connection between the client and the server – Rob Olmos Sep 04 '10 at 05:56

4 Answers4

12

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.

Marc B
  • 356,200
  • 43
  • 426
  • 500
  • 1
    Useful reading about persistent connections: http://www.mysqlperformanceblog.com/2006/11/12/are-php-persistent-connections-evil/ And this article about PDO: http://www.phpeveryday.com/articles/PDO-Improve-Performance-with-Persistent-Connection-P558.html – JMC Sep 04 '10 at 06:34
  • @user434493 - If you decide to go with a persistent connection to solve a network issue, staying with PDO is likely the smart move. – JMC Sep 04 '10 at 06:39
  • i am having a situation where i am returing result for a poll in percentages.I am confused which approach will be faster?calculating % in query itself or processing the percentage in php? – HIRA THAKUR Jun 19 '14 at 14:34
8

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
Dwza
  • 6,494
  • 6
  • 41
  • 73
  • This didn't make a single difference for me. I tested on Linux based system with sample 1 million rows - the results with "localhost" and 127.0.0.1 were identical. – pdolinaj Feb 17 '16 at 12:08
  • 1
    @pdolinaj its not the statement it self that takes longer.. its the executionstart. In my testings the `127.0.0.1` starts earlier with querying data than the `localhost` solution. – Dwza Feb 17 '16 at 13:25
  • 3
    For me this solution worked. query time reduced from 1 second to 9ms! – Namrata Tolani May 16 '17 at 14:28
8

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.

Ravish Kumar
  • 602
  • 1
  • 8
  • 20
shamittomar
  • 46,210
  • 12
  • 74
  • 78
  • So native mysql_ library is quicker than PDO? – nickb Sep 04 '10 at 05:37
  • 1
    @user434493, yes. Less abstraction, faster it becomes. – shamittomar Sep 04 '10 at 05:45
  • Doesn't your code above open my up to SQL Injection? It's that one of the main benefits of using PDO, that it protect you against security attacks on your SQL query string – nickb Sep 04 '10 at 05:56
  • Also, PDO offers PREPARED STATEMENTS and mysqli_connect doesn't. Doesn't that allow PDO to be faster? – nickb Sep 04 '10 at 05:57
  • 3
    mysqli is fast and is the suggested version to use by the manual at php.net (between: mysql & mysqli). I'm hoping someone will correct if I'm wrong, PDO's main advantage is it can connect to all types of databases not just mysql. – JMC Sep 04 '10 at 05:58
  • Would using a STORED PROCEDURE with MYSQLi be the fastest? – nickb Sep 04 '10 at 06:11
  • Since your servers are on different boxes, you should troubleshoot the connection before changing your code. You may not see much of an upgrade by changing to mysqli if the path is the root problem. – JMC Sep 04 '10 at 06:13
  • I think you meant "stands for" – wlf Nov 25 '12 at 18:10
  • @JMC mysqli is recommended over mysql if MySQL/MariaDB is the only database the system will connect to, as it has less abstraction than PDO. However, if the application may have to connect or must connect to multiple databases (or multiple database types), then PDO is recommended. Another advantage of PDO's abstraction is that all the database connectors present a very similar interface, thus code becomes more portable. – T0xicCode Dec 03 '12 at 05:59
  • if you make changes like i explained in my post, you will see pdo is as fast as mysqli. you just have more possibilitys to handle things with pdo. e.g. connecting to different DB's. i prefer pdo – Dwza Apr 07 '14 at 09:19
  • "Fastest possible"? How are you going to proof that? Where are the benchmark results? – Paul Spiegel May 17 '18 at 09:48
1

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 ;
Jon Black
  • 16,223
  • 5
  • 43
  • 42