Not so long ago, I came across a peculiar issue with PHP’s MySQLi extension.
For some strange reason, using the string “localhost” in the constructor will sometimes result in an unexpected delay in connectivity.
For example, connecting to MySQL will take 1-2 seconds. This is despite the fact that the DBMS is on a local network.
Judging from my own experience and from what others have said, this seems to be a problem that is exclusive to Windows 7.
To highlight the problem using code, switching this:
$mysql = new mysqli( 'localhost', 'root', 'pass', 'database' );
to
$mysql = new mysqli( 127.0.0.1, 'root', 'pass', 'database' );
…will completely fix the issue.
Another way of fixing this annoying problem is to edit the configuration file for MySQL (my.ini) and bind it to the IPV4 loopback adapter:
bind=127.0.0.1
This basically tells the server to operate on IPV4.
Like always, you’ll need to restart the MySQL server if you want any configuration changes to take effect.
Why is the MySQL connection so slow?
Basically, this happens because the MySQL client will do an IPV6 lookup for the hostname.
If this fails (and in this case, it obviously does), it will then attempt to do an IPV4 lookup.
Between the IPV6 (AAAA) lookup failing and the IPV4 (A) lookup succeeding, what we get is a connection timeout cycle that lasts about 1-2 seconds.
This timeout is extremely noticeable, especially when your database is local.
“I’ve never had this issue in the past.”
Before Windows 7, localhost resolution was handled by the hosts file, which came preconfigured with 127.0.0.1.
However, in Windows 7, Microsoft decided to let the DNS resolver handle the localhost resolution. This is because the world is moving towards IPV6 and companies will need to be able to disable and uninstall each IP version.