Slow data transfer of large result set

I have a l开发者_开发问答arge MySQL table, with proper indices etc. I run a simple select * query from a remote machine and I expect a large result set.

My problem is that when I run the query, the result set returns at a maximum data transfer speed of ~300 KBytes/sec. I created the same table, and run the same query on SQLServer Express 2008 R2, and the results returned at a transfer speed of 2MBytes/second (my line limit).

The server machine is Windows Server 2008 R2 x64, Quad core, 4GB RAM and the the MySQL version is 5.6.2 m5 64-bit. I tried disabling the compession in the communication protocol but the results where the same.

Does anyone have an idea as to why this is happening ?

--theodore

You might be comparing apples to oranges.

I'd run SELECT * on the MySQL server, and see what kind of data rate you get for retrieving data on the server locally -- without the additional constraint of a network.

If that's slow also -- then it isn't the network's fault.

When the MySQL setup program runs, it asks the person setting up MySQL what role MySQL is going to play on the hardware -- i.e., Development Server, Shared Server, Dedicated.

The difference in all of these is how much memory MySQL will seek to consume on the Server.

The slowest setting is Development (use the least memory), and the fastest one is Dedicated (attempt to use a lot of memory). You can tinker with the my.ini file to change how much memory MySQL will allocate for itself, and/or google 'my.ini memory' for more detailed instructions.

The memory that MySQL is using (or isn't, as the case may be), will make a huge difference on performance.

First, check to see what the speed is retrieving data locally on the MySQL server is. If it's slow, the network isn't the problem -- check MySQL's memory usage -- ideally give it as much as possible. And of course, if it's fast, then either the network and/or some piece of database middleware (ODBC?) or tool-used-to-display-the-data -- is slow...

One more thing -- try the SELECT * TWICE... why? The second time some or all of the results (again, depending on memory) should be cached... the second time it should be faster...

Also, don't forget to restart MySQL when changing the my.ini file (and create a backup before you make any changes...)