3

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

6
  • 1
    What is the ping time between the machines?
    – Rick James
    Jul 24, 2016 at 15:27
  • 4
    WHY do you want to fetch a million rows? WHAT can you do with that much data all at once? Can't you process the data in the database and return only a few rows?
    – Rick James
    Jul 24, 2016 at 15:27
  • Below is the ping time between the machine: Reply from 10.7.209.12: bytes=32 time<1ms TTL=128 Reply from 10.7.209.12: bytes=32 time<1ms TTL=128 Reply from 10.7.209.12: bytes=32 time<1ms TTL=128 Reply from 10.7.209.12: bytes=32 time<1ms TTL=128 Reply from 10.7.209.12: bytes=32 time<1ms TTL=128 We have a requirements to extract at least 5 thousand records at a time and process that. When we are extracting 5 thousand records then It takes times to give the results and sometime we get timeout or Lost connection error message. Please help on this.
    – Sujeet
    Jul 25, 2016 at 10:51
  • 3
    "When we are extracting 5 thousand records" Your query isn't doing that. It's fetching all of them. Jul 25, 2016 at 11:39
  • @Michal When you connect MySQL remotely and fetch 5000 records like select * from table_name LIMIT 5000 then Its still takes times and sometimes we get Lost connection error message.
    – Sujeet
    Jul 25, 2016 at 11:47

1 Answer 1

0

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

2
  • We are using PRIMARY KEY and It is still very slow. I have also used MySQLTuner to check the configuration and my configuration is also good as per Mysqltuner script.
    – Sujeet
    Jul 26, 2016 at 6:43
  • 1
    Having a PRIMARY KEY is not the same as using it for chunking. Please show the statement for the second 5000 rows.
    – Rick James
    Jul 26, 2016 at 14:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.