faster MySQL connections on Win NT/2000/XP

[DR_LaRRY_PEpPeR]

wow, i just discovered this yesterday. for testing stuff on my Win2k machine, i've always specified "localhost" as the hostname when connecting to MySQL.

i quit using persistent MySQL connections after having weird quirks with them. then i noticed that my page creation times were slower (by about 1/100th of a second!), of course, since a new MySQL connection had to be created each time. but on my Linux host, persistent connections are disabled, and it connects to MySQL just as fast as my system did with persistent connections on. i chalked that up to MySQL being faster on *nix systems and that, by default, MySQL connections use Unix sockets.

yesterday, in the MySQL manual i saw that on Windows if you specify "localhost," it will connect with TCP/IP, but on Win NT if you specify "." as the hostname (or leave it blank), it will connect using named-pipes. i tried it, and it connected as fast as with persistent connections before!

here's the time it takes to connect with the different methods, according to my tests:

Hostname    Non-persistent    Persistent

localhost       10.5ms          0.4ms
.                0.6ms          0.25ms

that's with MySQL's thread_cache enabled. it takes the non-persistent connections ~0.3ms longer to connect with no thread_cache.

that's amazing, though! just changing that makes it connect 1/100th of a second faster. when it's this fast i don't care about pconnect()'s.

the couple queries that i've checked are running 0.1-0.4ms faster now too (almost as fast as on Linux).

it's just on my test machine and it doesn't really matter. i just like things to be super fast. so i thought i'd pass this along (in case anybody's running actual servers on Windows). to use these named-pipe connections, you have to be using NT/2000/XP and be running mysqld-nt or mysqld-max-nt.

[i_like_php]

i know the feeling i love the speed

[DR_LaRRY_PEpPeR]

update on this: named pipes have been disabled by default in the newest versions of MySQL (since 3.23.50). i just installed 3.23.51 today (never saw 2.23.50 ) and got "Cannot connect" errors with '.'. here's what it says in the changelog:

Because of problems on shutdown we have now disabled named pipes on windows by default. One can enable named pipes by starting mysqld with --enable-named-pipe.

i don't know what they mean by "problems." anyway, to re-enable faster named pipes when you're running MySQL as a service, just add

enable-named-pipe

to your my.cnf/my.ini under the [mysqld] section.