CREATE TABLE `ngrams` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `objectID` int(10) unsigned NOT NULL,
  `ngram` char(3) COLLATE utf8mb4_bin NOT NULL,
  PRIMARY KEY (`id`),
  KEY `objectID` (`objectID`),
  KEY `ngram` (`ngram`,`objectID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

<?php

require_once 'scripts/__init_script__.php';

$ngram_table = 'ngrams';

$table = new PhabricatorFile();
$conn_w = $table->establishConnection('w');
foreach (new LiskMigrationIterator($table) as $file) {
  $name = $file->getName();
  $id = $file->getID();

  $ngrams = get_ngrams($name);

  $visible = array();
  foreach ($ngrams as $ngram) {
    $visible[] = '<'.$ngram.'>';
  }

  echo "{$name} -> ".implode(', ', $visible)."\n";

  $sql = array();
  foreach ($ngrams as $ngram) {
    $sql[] = qsprintf(
      $conn_w,
      '(%d, %s)',
      $file->getID(),
      $ngram);
  }

  queryfx(
    $conn_w,
    'DELETE FROM %T WHERE objectID = %d',
    $ngram_table,
    $id);

  if (!$sql) {
    continue;
  }

  queryfx(
    $conn_w,
    'INSERT INTO %T (objectID, ngram) VALUES %Q',
    $ngram_table,
    implode(', ', $sql));
}

function get_ngrams($str) {
  if (!strlen($str)) {
    return array();
  }

  $str = phutil_utf8_strtolower($str);
  $str = preg_replace('/ +/', ' ', $str);
  $str = ' '.$str.' ';
  $str = phutil_utf8v($str);

  $ngrams = array();
  $count = (count($str) - 2);
  for ($ii = 0; $ii < $count; $ii++) {
    $ngram = $str[$ii].$str[$ii + 1].$str[$ii + 2];
    $ngrams[$ngram] = $ngram;
  }

  return $ngrams;
}

<?php

require_once 'scripts/__init_script__.php';

$ngram_table = 'ngrams';

$table = new PhabricatorFile();
$conn_r = $table->establishConnection('r');

$query = $argv[1];

$ngrams = get_ngrams($query);

$joins = array();
$idx = 0;
foreach ($ngrams as $ngram) {
  $alias = 'ngram_'.$idx++;
  $joins[] = qsprintf(
    $conn_r,
    'JOIN %T %T ON %T.objectID = file.id AND %T.ngram = %s',
    $ngram_table,
    $alias,
    $alias,
    $alias,
    $ngram);
}

$full_ngrams = qsprintf(
  $conn_r,
  'SELECT file.id FROM file %Q
    WHERE name LIKE %~
    GROUP BY file.id',
  implode(' ', $joins),
  $query);

$full_like = qsprintf(
  $conn_r,
  'SELECT file.id FROM file
    WHERE name LIKE %~',
    $query);

$queries = array(
  'ngram' => $full_ngrams,
  'like' => $full_like,
);

$results = array();
foreach ($queries as $key => $raw_query) {
  echo ">>> ".$raw_query."\n";

  $t_start = microtime(true);
  $rows = queryfx_all($conn_r, '%Q', $raw_query);
  $t_end = microtime(true);

  $rows = ipull($rows, 'id');
  sort($rows);
  $results[$key] = array(
    'time' => ($t_end - $t_start),
    'rows' => $rows,
  );
}


if ($results['ngram']['rows'] !== $results['like']['rows']) {
  var_dump($results);
  throw new Exception('Different results!');
}

echo "Got ".count($results['ngram']['rows'])." results.\n";
echo "Same results.\n";
echo "LIKE Time: ".((int)(1000000 * $results['like']['time']))."us\n";
echo "NGRAM Time: ".((int)(1000000 * $results['ngram']['time']))."us\n";


function get_ngrams($str) {
  if (!strlen($str)) {
    return array();
  }

  $str = phutil_utf8_strtolower($str);
  $str = preg_replace('/ +/', ' ', $str);
//  $str = ' '.$str.' ';
  $str = phutil_utf8v($str);

  $ngrams = array();
  $count = (count($str) - 2);
  for ($ii = 0; $ii < $count; $ii++) {
    $ngram = $str[$ii].$str[$ii + 1].$str[$ii + 2];
    $ngrams[$ngram] = $ngram;
  }

  return $ngrams;
}

$ php -f query.php rainbow
>>> SELECT file.id FROM file JOIN `ngrams` `ngram_0` ON `ngram_0`.objectID = file.id AND `ngram_0`.ngram = 'rai' JOIN `ngrams` `ngram_1` ON `ngram_1`.objectID = file.id AND `ngram_1`.ngram = 'ain' JOIN `ngrams` `ngram_2` ON `ngram_2`.objectID = file.id AND `ngram_2`.ngram = 'inb' JOIN `ngrams` `ngram_3` ON `ngram_3`.objectID = file.id AND `ngram_3`.ngram = 'nbo' JOIN `ngrams` `ngram_4` ON `ngram_4`.objectID = file.id AND `ngram_4`.ngram = 'bow'
    WHERE name LIKE '%rainbow%'
    GROUP BY file.id
>>> SELECT file.id FROM file
    WHERE name LIKE '%rainbow%'
Got 23 results.
Same results.
LIKE Time: 77180us
NGRAM Time: 1049us

mysql> select ngram, count(*) N from ngrams group by ngram order by N desc limit 10;
+-------+-------+
| ngram | N     |
+-------+-------+
| ile   | 76440 |
| fil   | 76352 |
| pro   | 74372 |
| rof   | 73567 |
| ofi   | 73491 |
| .jp   | 68672 |
| jpg   | 68472 |
| pg    | 68281 |
| le.   | 68131 |
| -pr   | 67232 |
+-------+-------+
10 rows in set (0.91 sec)

$ php -f query.php ile
>>> SELECT file.id FROM file JOIN `ngrams` `ngram_0` ON `ngram_0`.objectID = file.id AND `ngram_0`.ngram = 'ile'
    WHERE name LIKE '%ile%'
    GROUP BY file.id
>>> SELECT file.id FROM file
    WHERE name LIKE '%ile%'
Got 76439 results.
Same results.
LIKE Time: 146003us
NGRAM Time: 280315us

$ php -f query.php profile.jpg
>>> SELECT file.id FROM file JOIN `ngrams` `ngram_0` ON `ngram_0`.objectID = file.id AND `ngram_0`.ngram = 'pro' JOIN `ngrams` `ngram_1` ON `ngram_1`.objectID = file.id AND `ngram_1`.ngram = 'rof' JOIN `ngrams` `ngram_2` ON `ngram_2`.objectID = file.id AND `ngram_2`.ngram = 'ofi' JOIN `ngrams` `ngram_3` ON `ngram_3`.objectID = file.id AND `ngram_3`.ngram = 'fil' JOIN `ngrams` `ngram_4` ON `ngram_4`.objectID = file.id AND `ngram_4`.ngram = 'ile' JOIN `ngrams` `ngram_5` ON `ngram_5`.objectID = file.id AND `ngram_5`.ngram = 'le.' JOIN `ngrams` `ngram_6` ON `ngram_6`.objectID = file.id AND `ngram_6`.ngram = 'e.j' JOIN `ngrams` `ngram_7` ON `ngram_7`.objectID = file.id AND `ngram_7`.ngram = '.jp' JOIN `ngrams` `ngram_8` ON `ngram_8`.objectID = file.id AND `ngram_8`.ngram = 'jpg'
    WHERE name LIKE '%profile.jpg%'
    GROUP BY file.id
>>> SELECT file.id FROM file
    WHERE name LIKE '%profile.jpg%'
Got 64650 results.
Same results.
LIKE Time: 144378us
NGRAM Time: 1389069us