MySQL Federated storage engine vs replication (performance)

Question

Long story short - I am dealing with a largish database where basic user details (userid (index), username, password, parent user, status) are stored in one database and extended user details (same userid (index), full name, address etc. etc.) are stored in another database on another server.

I need to do a query where I select all users owned by a particular user (via the parent user field from basic user details database), sorted by their full name (from the extended user details field) and just 25 at a time (there are thousands, maybe tens of thousands for any one user).

As far as I can work out there are three possible solutions;

1. No JOIN - get all the user IDs in one query and run the second query based on those IDs. This would be fine, except the number of user IDs could get so high that it would exceed the maximum query length, or be horribly inefficient.

2. Replicate the database table with the basic user details onto the server with the extended details so I can do a JOIN

3. Use a federated storage engine table to achieve the same results as #2

It seems that 3 is the best option, but I have been able to find little information about performance and I also found one comment to be careful using this on production databases.

I would appreciate any suggestions on what would be the best implementation.

Thanks!

Answer 1

Talking in terms of performance or limitations, Federated Engine has a lot of limitations. It doesn't support for transactions, Performance on a FEDERATED table when performing bulk inserts is slower than with other table types etc..

Replication and Federated engines are not meant to do same things. First of all, did you try both?

Answer 2

FEDERATED tables are a nice feature .. but they do not support indexes, which would slow down your application dramatically.

if (!) you do read only from the users database on the remote server. replication would be more effective and also faster.