generator.php

<?php

$tables = array(
    'mytable' => array(
        'cols' => array(
            'description'   => 'LONGTEXT NULL DEFAULT NULL',
        ),
        'index' => array(
            'fulltext_search'   => array('description'),
        )
    ),
);

function quote($str) {
    return "`$str`";
}

foreach ($tables as $table => $settings) {
    $last_col = array_keys($settings['cols'])[count($settings['cols']) - 1];
    ob_start(); ?>
-- Create table with only the needed fields
DROP TABLE IF EXISTS <?php echo $table ?>_search;
CREATE TABLE `<?php echo $table ?>_search` (
  `id` int(11) NOT NULL,
<?php foreach ($settings['cols'] as $col => $def): ?>
  <?php echo "`$col` $def,"; ?>

<?php endforeach ?>
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


-- First populate without indices for fast inserts
INSERT INTO <?php echo $table ?>_search (
    `id`,
    <?php echo implode(",\n    ", array_map('quote', array_keys($settings['cols']))) ?>

  ) SELECT
    source.`id`,
    source.<?php echo implode(",\n    source.", array_map('quote', array_keys($settings['cols']))) ?>

    FROM <?php echo $table ?> source
    ;

<?php foreach ($settings['index'] as $index => $cols): ?>
CREATE FULLTEXT INDEX `<?php echo $index ?>` ON <?php echo $table ?>_search (<?php echo implode(',',array_map('quote', $cols)) ?>);

<?php endforeach ?>

-- Percona cluster does not support replication of inline NEW.*
-- So we must declare variables

DELIMITER //
DROP TRIGGER IF EXISTS `<?php echo $table ?>_search_insert`//
CREATE TRIGGER `<?php echo $table ?>_search_insert` AFTER INSERT ON `<?php echo $table ?>`
FOR EACH ROW BEGIN
  DECLARE id_val INT;
<?php foreach ($settings['cols'] as $col => $def): ?>
  DECLARE <?php echo "{$col}_val ".preg_replace('/ NULL.*$/', '', $def).";"; ?>

<?php endforeach ?>
  SET id_val = NEW.`id`;
<?php foreach ($settings['cols'] as $col => $def): ?>
  SET <?php echo "{$col}_val = NEW.`{$col}`;"; ?>

<?php endforeach ?>

  INSERT INTO <?php echo $table ?>_search (
    `id`,
    <?php echo implode(",\n    ", array_map('quote', array_keys($settings['cols']))) ?>

  ) VALUES (
    id_val,
<?php foreach ($settings['cols'] as $col => $def): ?>
    <?php echo "{$col}_val"; if ($col != $last_col) echo ','; ?>

<?php endforeach ?>
  );
END
//
DELIMITER ;

DELIMITER //
DROP TRIGGER IF EXISTS `<?php echo $table ?>_search_update`//
CREATE TRIGGER `<?php echo $table ?>_search_update` AFTER UPDATE ON `<?php echo $table ?>`
FOR EACH ROW BEGIN
  DECLARE id_val INT;
<?php foreach ($settings['cols'] as $col => $def): ?>
  DECLARE <?php echo "{$col}_val ".preg_replace('/ NULL.*$/', '', $def).";"; ?>

<?php endforeach ?>
  SET id_val = NEW.`id`;
<?php foreach ($settings['cols'] as $col => $def): ?>
  SET <?php echo "{$col}_val = NEW.`{$col}`;"; ?>

<?php endforeach ?>

  UPDATE <?php echo $table ?>_search SET
<?php foreach ($settings['cols'] as $col => $def): ?>
    <?php echo "`$col` = {$col}_val"; if ($col != $last_col) echo ','; ?>

<?php endforeach ?>
  WHERE `id` = id_val;
END
//
DELIMITER ;

DELIMITER //
DROP TRIGGER IF EXISTS `<?php echo $table ?>_search_delete`//
CREATE TRIGGER `<?php echo $table ?>_search_delete` BEFORE DELETE ON `<?php echo $table ?>`
FOR EACH ROW BEGIN
  DECLARE id_val INT;
  SET id_val = OLD.`id`;
  DELETE FROM <?php echo $table ?>_search WHERE `id` = id_val;
END
//
DELIMITER ;

-- Insert missing data that could have be inserted in original table since the first insert
INSERT INTO <?php echo $table ?>_search (
    `id`,
    <?php echo implode(",\n    ", array_map('quote', array_keys($settings['cols']))) ?>

  ) SELECT
    source.`id`,
    source.<?php echo implode(",\n    source.", array_map('quote', array_keys($settings['cols']))) ?>

    FROM <?php echo $table ?> source
  ON DUPLICATE KEY UPDATE
<?php foreach ($settings['cols'] as $col => $def): ?>
    <?php echo "`$col` = source.`$col`"; if ($col != $last_col) echo ','; ?>

<?php endforeach ?>
    ;

<?php
    $sql = ob_get_contents();
    ob_end_clean();
    // echo $sql;
    file_put_contents(__DIR__ . "/$table.sql", $sql);
}

myisam-fulltext-triggers.sql

-- Create table with only the needed fields
DROP TABLE IF EXISTS mytable_search;
CREATE TABLE `mytable_search` (
  `id` int(11) NOT NULL,
  `description` LONGTEXT NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


-- First populate without indices for fast inserts
INSERT INTO mytable_search (
    `id`,
    `description`
  ) SELECT
    source.`id`,
    source.`description`
    FROM mytable source
    ;

CREATE FULLTEXT INDEX `fulltext_search` ON mytable_search (`description`);


-- Percona cluster does not support replication of inline NEW.*
-- So we must declare variables

DELIMITER //
DROP TRIGGER IF EXISTS `mytable_search_insert`//
CREATE TRIGGER `mytable_search_insert` AFTER INSERT ON `mytable`
FOR EACH ROW BEGIN
  DECLARE id_val INT;
  DECLARE description_val LONGTEXT;
  SET id_val = NEW.`id`;
  SET description_val = NEW.`description`;

  INSERT INTO mytable_search (
    `id`,
    `description`
  ) VALUES (
    id_val,
    description_val
  );
END
//
DELIMITER ;

DELIMITER //
DROP TRIGGER IF EXISTS `mytable_search_update`//
CREATE TRIGGER `mytable_search_update` AFTER UPDATE ON `mytable`
FOR EACH ROW BEGIN
  DECLARE id_val INT;
  DECLARE description_val LONGTEXT;
  SET id_val = NEW.`id`;
  SET description_val = NEW.`description`;

  UPDATE mytable_search SET
    `description` = description_val
  WHERE `id` = id_val;
END
//
DELIMITER ;

DELIMITER //
DROP TRIGGER IF EXISTS `mytable_search_delete`//
CREATE TRIGGER `mytable_search_delete` BEFORE DELETE ON `mytable`
FOR EACH ROW BEGIN
  DECLARE id_val INT;
  SET id_val = OLD.`id`;
  DELETE FROM mytable_search WHERE `id` = id_val;
END
//
DELIMITER ;

-- Insert missing data that could have be inserted in original table since the first insert
INSERT INTO mytable_search (
    `id`,
    `description`
  ) SELECT
    source.`id`,
    source.`description`
    FROM mytable source
  ON DUPLICATE KEY UPDATE
    `description` = source.`description`
    ;

query-example.sql

-- Example of the new query
SELECT t.*
  FROM `mytable` t
  JOIN `mytable_search` s ON (s.id = t.id)
  WHERE MATCH(s.description) AGAINST ('mysearch');