Federated engine – MySQL table as symlink

db, development Add comments

Are you aware of Federated engine in MySQL (apart from MyISAM and InnoDB)?

This engine allows you to define a table that sucks data from another table, even from a remore server. The tables definition must be the same.

I use it for the following:

  1. Every time I rebuild the project, I have wait for 15 minutes while two big tables are created and filled with data — these are geo data tables (world cities, regions, etc), 4 mln records, and POI table, 2 mln records. I use Federated tables to create two separate databases and just link these tables in my project.
  2. These tables are shared between several environments (dev, test and live) on the same server.

To check if your MySQL server has the Federated engine supported, you can use just a phpMyAdmin — go to home page of you phpMyAdmin installation (click Home picture), then choose Engines tab and check there.

If it’s not enabled (gray), open your my.ini file, find the “[mysqld]” part and make it to look like this:

[mysqld]
federated

P.S. If you have an error in the table definition, phpMyAdmin shows your database as empty. To fix this, log in via mysql console and try to make a SELECT from this poorly defined table and you get the error message to work with.

Comments are closed.

WP Theme & Icons by N.Design Studio
Entries RSS Comments RSS Log in