Slow MySQLi Connection

A lot of people (including myself) have come 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. i.e. Connecting to MySQL will take 1-2 seconds, even if the DBMS you’re trying to connect to is hosted on your local machine. Judging from my own experience and from what others have said, this seems to be a problem that is exclusive to Windows 7 (if I am mistaken, feel free to correct me in the comments section below).

To highlight the problem using code, switching from something like:

1	$mysql = new mysqli( 'localhost', 'root', 'pass', 'database' );

to

1	$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 to the IPV4 loopback adapter:

1	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.

OK, but WHY is this happening?

Basically, this is caused 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. It is safe to say that this timeout is extremely noticeable, especially when your database is located on a stress-free local server.

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, simply because the world is moving towards IPV6 and companies will need to be able to disable/uninstall each IP version in an independent fashion.