Windows – Connecting to MySQL from PHP is extremely slow

MySQLPHPwindowsxampp

I just did a fresh install of XAMPP. When first opening PHPMyAdmin I noticed it was extremely slow. It didn't make sense that on localhost it should take almost 5 seconds for every page to open. I made a small test case to shift the blame off PHPMyAdmin:

$con = new PDO("mysql:host=localhost;dbname=mysql", "root", "");
$statement = $con->query('SELECT host,user,password FROM user;');
$users = $statement->fetchAll(PDO::FETCH_ASSOC);

The above script takes just about 3 seconds to run (although it took closer to 8 seconds to load the first time I ran it.)

Then to check if it was PDO's fault I tried using mysql_connect instead:

$con = mysql_connect("localhost", "root", "");
mysql_select_db("mysql", $con);
$result = mysql_query('SELECT host,user,password FROM user;');

Takes exactly as long to finish.

I thought it was PHP's fault at first, but PHP code and static files are served snappier than I can click refresh. I tested PHP by running this little script:

header("Content-Type: text/plain");

for($i = 0; $i < 5000; $i++)
{
    echo sha1(rand()) . "\n";
}

5000 sha1 calculations and the page is still displayed snappier than I can refresh my window.

Then I figured it was MySQL's fault. But again, didn't take much testing to figure out that MySQL is working faster than I need it to. Using the MySQL CLI client the user select query doesn't even take measurable time – it's done before I've even let the return key up.

The issue must be PHP's connection to MySQL – that's as far as I've been able to reason. I can find tons of stuff about PHP being slow or MySQL being slow, but nothing about PHP+MySQL being extremely slow.

Thanks to anyone who can help me solve this!


I'm using XAMPP 1.8.0 for win32 (Download link)
PHP version: 5.4.4
MySQL version: 14.14


EDIT: After timing, it turns out it's the connect function that's taking so long:

$time = microtime(true);

$con = mysql_connect("localhost", "root", "");
mysql_select_db("mysql", $con);

$con_time = microtime(true);

$result = mysql_query('SELECT host,user,password FROM user;');

$sel_time = microtime(true);

printf("Connect time: %f\nQuery time: %f\n",
       $con_time-$time,
       $sel_time-$con_time);

Output:

Connect time: 1.006148
Query time: 0.000247

What can cause PHP to spend do much time connecting to the database? The CLI client, HeidiSQL and MySQL workbench connect instantly

Best Answer

Can it be that your mysql tries to run rev-dns query whenever you connect? try adding to my.cnf, section mysqld: skip-name-resolve.