<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="federated-create">
      </a>
      18.8.2 How to Create FEDERATED Tables
     </h3>
    </div>
   </div>
  </div>
  <div class="toc">
   <dl class="toc">
    <dt>
     <span class="section">
      <a href="federated-create-connection.html">
       18.8.2.1 Creating a FEDERATED Table Using CONNECTION
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="federated-create-server.html">
       18.8.2.2 Creating a FEDERATED Table Using CREATE SERVER
      </a>
     </span>
    </dt>
   </dl>
  </div>
  <p>
   To create a
   <code class="literal">
    FEDERATED
   </code>
   table you should follow
      these steps:
  </p>
  <div class="orderedlist">
   <ol class="orderedlist" type="1">
    <li class="listitem">
     <p>
      Create the table on the remote server. Alternatively, make a
          note of the table definition of an existing table, perhaps
          using the
      <a class="link" href="show-create-table.html" title="15.7.7.11 SHOW CREATE TABLE Statement">
       <code class="literal">
        SHOW CREATE TABLE
       </code>
      </a>
      statement.
     </p>
    </li>
    <li class="listitem">
     <p>
      Create the table on the local server with an identical table
          definition, but adding the connection information that links
          the local table to the remote table.
     </p>
    </li>
   </ol>
  </div>
  <p>
   For example, you could create the following table on the remote
      server:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa1427744"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> test_table <span class="token punctuation">(</span>
    id     <span class="token datatype">INT</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">AUTO_INCREMENT</span><span class="token punctuation">,</span>
    <span class="token keyword">name</span>   <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">32</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">DEFAULT</span> <span class="token string">''</span><span class="token punctuation">,</span>
    other  <span class="token datatype">INT</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">DEFAULT</span> <span class="token string">'0'</span><span class="token punctuation">,</span>
    <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span>  <span class="token punctuation">(</span>id<span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">INDEX</span> <span class="token keyword">name</span> <span class="token punctuation">(</span><span class="token keyword">name</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">INDEX</span> other_key <span class="token punctuation">(</span>other<span class="token punctuation">)</span>
<span class="token punctuation">)</span>
<span class="token keyword">ENGINE</span><span class="token operator">=</span>MyISAM
<span class="token keyword">DEFAULT</span> <span class="token keyword">CHARSET</span><span class="token operator">=</span>utf8mb4<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   To create the local table that is federated to the remote table,
      there are two options available. You can either create the local
      table and specify the connection string (containing the server
      name, login, password) to be used to connect to the remote table
      using the
   <code class="literal">
    CONNECTION
   </code>
   , or you can use an
      existing connection that you have previously created using the
   <a class="link" href="create-server.html" title="15.1.18 CREATE SERVER Statement">
    <code class="literal">
     CREATE SERVER
    </code>
   </a>
   statement.
  </p>
  <div class="important" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Important
   </div>
   <p>
    When you create the local table it
    <span class="emphasis">
     <em>
      must
     </em>
    </span>
    have an identical field definition to the remote table.
   </p>
  </div>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    You can improve the performance of a
    <code class="literal">
     FEDERATED
    </code>
    table by adding indexes to the
        table on the host. The optimization occurs because the query
        sent to the remote server includes the contents of the
    <code class="literal">
     WHERE
    </code>
    clause and is sent to the remote server
        and subsequently executed locally. This reduces the network
        traffic that would otherwise request the entire table from the
        server for local processing.
   </p>
  </div>
 </div>
 <br/>
</div>
