Why Federatedx Engine is very slow in MariaDB 10.6 and have to read all the table to get only first 10 rows?

Question

I am trying to get 10 rows from one MariaDB to another MariaDB. However, It takes one and half minute.

I have created the table with federated engine pointing to remote MariaDB. Including all the indexes.

I have tried following points without success:

• get only 5 columns
• Use both plugins federatedx and federated
• get 10 first rows including in where indexed date, reduce the execution to 45 seconds. But still is slow.

my.cnf has default values in both sides: [mysqld] datadir=D:/MARIADB/data port=3306 innodb-page-size=65536 innodb_buffer_pool_size=7G character-set-server=utf8 event_scheduler=ON [client] port=3306 plugin-dir=D:/MARIADB/lib/plugin

Answer 1

FInally, I found the issue reading old answer and analizing volume of information transfered between servers: SELECT in federated table is to slow

When you have federatedX tables and you run directly the select getting the first 10 rows (select * from table1 limit 10; ) . All table is moved from host server to remote server, in my case the size were 3GB it was taking (45 seconds x 500 Mbps = 25000 Mbits / 8 = 3.12GB aprox) to move that is the total size of the table. After move all the table the MariaDB destination get the first 10 rows making it unefficient:

The solution was to use an indexed column (as recommended by Verace) in WHERE statement as suggested in old post. Using the indexed column in WHERE statement, FEDERATEDX engine moves only the registers in index from server to server. Using the indexed column the query has taken only 1.938 seconds and moved the information of one day between servers in my case 100 MB.

SELECT * FROM db.table1 WHERE start_date='2021-07-14' LIMIT 10