Integration of 2 MySQL databases



  • There are two MySQL databases on different servers (A and B) in each 60+ of tables with 100 to 1 miles of records. There is a challenge to make a violin to collect all data in the third base X on a separate server.

    Now request looks like:

    $max_id = $mysqliX->query("SELECT id FROM table ORDER BY id DESC LIMIT 1"); //id - Primary key и автоинкрементное
    $table_max_id = $max_id->fetch_row(); // получаем $table_max_id[0]  
    

    $new_entries = $mysqliA->query("SELECT * FROM table WHERE id > $table_max_id[0]"); //выбираем все новые значения

    Next to the cycle. while I'm writing everything into X.
    I'll do the same for the second base.
    As a result, it's going on for a long time.

    Even the script counting of new records, which instead SELECT and bets just use it. SELECT count() It's 3-4 minutes.

    Can you tell me how to speed up?
    Can one request place a spleen from different bases? And will that help?
    Prepared INSERT will speed up the process?



  • The box can be accelerated.

    Option 1♪ Making a box not on one record, but by groups, for example, 1,000 records, requests will be sorted

    INSERT INTO your_table (col1, col2, col3, ...)
    VALUES
    (value11, value12, value13, ...),
    (value21, value22, value23, ...),
    ...
    (valueN1, valueN2, valueN3, ...)
    

    Option 2♪ Loading data with help http://dev.mysql.com/doc/refman/5.7/en/load-data.html




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2