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
$new_entries = $mysqliA->query("SELECT * FROM table WHERE id > $table_max_id"); //выбираем все новые значения
Next to the cycle.
whileI'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
SELECTand 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?
INSERTwill speed up the process?
Analeea last edited by
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