Running a MySQL query on a local database takes only about 20-30ms but running the same query through a remote connection(internet) takes 500ms. Is this normal? If not what could be the possible reason for such delay?
This is a wireshark time extract for a particular Query run remotely.
Time -195.768993000 --- Request Ping
Time -195.909453000 --- Response OK
Time -195.909729000 --- Request Use Database
Time -196.049567000 --- Response OK
Time -196.049918000 --- Request Query
Time -196.219660000 --- Response
NOTE: I tried running the same query remotely through workbench and the duration of execution was 200ms.This I think matches time taken from 'Request Query' to 'Response' in C# as shown in the wireshark data above.
Talk1:
how many rows it will return ?
Talk2:
May be 100. But that doesn't matter as "Request Ping to Request Query" only takes about 300ms.
Talk3:
can't you check the query by running directly on server with ssh
Talk4:
If I am right SSH only encrypts your connection it will not increase the throughput.
Talk5:
yes but it not needs to download the data to your local server, it will show you correct time of execution
Solutions1
It could be because of a DNS problem. You can disable DNS host name lookups by starting mysqld with the --skip-name-resolve option in the configuration file.
Talk1:
You checked again after restarting the server, right?
Talk2:
Ya, it seems that a remote connection would always have that time difference.