Mysql – SELECT Query is slow when run remotely in MySQL

MySQLperformancequery-performance

When I connect MySQL from remote server and run below query then the results are very slow(Sometime even faced lost connection Issue). However when I run the same SQL query on the local machine then It's results are very fast.

    SELECT * FROM CUSTOMERS

The customer tale contains two million records.

I have also enable skip-host-cache and skip-name-resolve in MySQL configuration.

Both machine is connected using the LAN IP and customer table is InnoDB table. I am using MySQL 5.6.

Can you please suggest me on this, why SQL query are very slow when run It from remote machine.

Thank you,
Sujeet

Best Answer

LIMIT will work for the first 5000, but will get slower and slower as you move on (with OFFSET) to subsequent chunks. So that's not a good way to process a large table in chunks.

Does the table have a PRIMARY KEY? (If not, why not?) Use that for very efficient chunking. It will avoid the timeouts you are getting.

A discussion of chunking. (That is aimed at DELETE, but it can easily be adapted to SELECT.) The trick is to "remember where you left off".

Related Solutions

Mysql – Connection to MySQL Remotely

You state that "Connections, ports, user accounts and credentials are all fine.", but I have my doubts.

Mentioning the exact error from the documentation:

[The error] occurs when you try to connect from a host other than the one on which the MySQL server is running, it means that there is no row in the user table with a Host value that matches the client host

Look in your mysql.user table on the Linux MySQL server for a user entry with the user/host combination of your windows machine:

SELECT User, Host FROM mysql.user WHERE User='my_user' AND Host='windows_ip';

If that doesn't return a result, you'll need to add that user. If it does return a result, can you add that to your question?

Thesql import/drop very slow on pyshycal server

It is important to know the load average in your MySQL server, in the VMWare server with Percona DB, the server (i assume) has no working tasks. but in the MySQL server you have some extra Work. If your MySQL server is on a unix/linux server you can check your load average with the command top-i or simple use up time.

Another think you can check is how many connections do you have in your MySQL and your Connection Usage, the traffic, number of Queries, Query Caché,etc.

I hope this can help you to take a direction of way do you have this problem.s Please update with more information if you still need more help.

Best Answer

Related Solutions

Mysql – Connection to MySQL Remotely

Thesql import/drop very slow on pyshycal server

Related Question