data.sql

SELECT COUNT(*) FROM test.sample; // 3 000 000

SELECT * FROM test.sample LIMIT 3;
/*
1,"Some lond1 text1 with many1 unique1 words"
2,"Some lond2 text2 with many2 unique2 words"
3,"Some lond3 text3 with many3 unique3 words"
*/

INSERT INTO test.fts_doc_id (product_id, data_index) SELECT product_id, data_index FROM test.sample;
INSERT INTO test.id (product_id, data_index) SELECT product_id, data_index FROM test.sample;

EXPLAIN UPDATE `test`.`id` SET `product_id` = 1000, `data_index` = 'Some lond1000 text1000 with many1000 unique1000 words' WHERE `product_id` = 1000;
/* 1,SIMPLE,id,range,product_id,product_id,4,const,1,"Using where; Using temporary" */

EXPLAIN DELETE FROM test.`fts_doc_id` WHERE `product_id` = 1000;
/* 1,SIMPLE,fts_doc_id,range,product_id,product_id,4,const,1,"Using where" */

fts_doc_id.sql

CREATE TABLE `fts_doc_id` (
  `FTS_DOC_ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Entity ID',
  `product_id` int(10) unsigned NOT NULL COMMENT 'Product ID',
  `data_index` longtext COMMENT 'Data index',
  PRIMARY KEY (`FTS_DOC_ID`),
  KEY `product_id` (`product_id`),
  FULLTEXT KEY `FTI_CATALOGSEARCH_FULLTEXT_DATA_INDEX_1` (`data_index`)
) ENGINE=InnoDB AUTO_INCREMENT=4301551 DEFAULT CHARSET=utf8 COMMENT='catalogsearch_fulltext_index_default';

id.sql

CREATE TABLE `id` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Entity ID',
  `product_id` int(10) unsigned NOT NULL COMMENT 'Product ID',
  `data_index` longtext COMMENT 'Data index',
  PRIMARY KEY (`id`),
  KEY `product_id` (`product_id`),
  FULLTEXT KEY `FTI_CATALOGSEARCH_FULLTEXT_DATA_INDEX_2` (`data_index`)
) ENGINE=InnoDB AUTO_INCREMENT=3014611 DEFAULT CHARSET=utf8 COMMENT='catalogsearch_fulltext_index_default';

result.sh

$ time php id.php

real    0m4.322s
user    0m0.208s
sys     0m0.024s
$ time php fts_doc_id.php

real    1m23.549s
user    0m3.972s
sys     0m2.700s
$ time php id_group.php

real    0m55.149s
user    0m3.264s
sys     0m1.920s
$ time php fts_doc_id_group.php

real    9m34.893s
user    0m28.676s
sys     0m15.020s

test_db.php

<?php
/**
 * Copyright © 2015 Magento. All rights reserved.
 * See COPYING.txt for license details.
 */

$dsn = 'mysql:host=localhost;dbname=test';
$username = 'root';
$password = '123456';
$options = array(
    PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
);

$dbh = new \PDO($dsn, $username, $password, $options);

test_fts_doc_id.php

<?php
/**
 * Copyright © 2015 Magento. All rights reserved.
 * See COPYING.txt for license details.
 */

require __DIR__ . '/db.php';

$n = 1000;
for ($i = 0; $i < 100000; $i++) {
    $dbh->beginTransaction();
    $dbh->exec('DELETE FROM `fts_doc_id` WHERE `product_id` = ' . $n);
    $dbh->exec("INSERT INTO `fts_doc_id` (`product_id`, `data_index`) VALUES({$n}, 'Some lond{$n} text{$n} with many{$n} unique{n} words')");
    $dbh->commit();
}

test_fts_doc_id_group.php

<?php
/**
 * Copyright © 2015 Magento. All rights reserved.
 * See COPYING.txt for license details.
 */

require __DIR__ . '/db.php';

for ($i = 0; $i < 100000; $i++) {
    $dbh->beginTransaction();
    for ($n = 1000; $n <= 1010; $n++) {
        $dbh->exec('DELETE FROM `fts_doc_id` WHERE `product_id` = ' . $n);
        $dbh->exec("INSERT INTO `fts_doc_id` (`product_id`, `data_index`) VALUES({$n}, 'Some lond{$n} text{$n} with many{$n} unique{n} words')");
    }
    $dbh->commit();
}

test_id.php

<?php
/**
 * Copyright © 2015 Magento. All rights reserved.
 * See COPYING.txt for license details.
 */

require __DIR__ . '/db.php';

$n = 1000;
for ($i = 0; $i < 10000; $i++) {
    $dbh->exec("UPDATE `id` SET `product_id` = {$n}, `data_index` = 'Some lond{$n} text{$n} with many{$n} unique{$n} words' WHERE `product_id` = {$n}");
}

test_id_group.php

<?php
/**
 * Copyright © 2015 Magento. All rights reserved.
 * See COPYING.txt for license details.
 */

require __DIR__ . '/db.php';

for($i = 0; $i < 10000; $i++) {
    for ($n = 1000; $n <= 1010; $n++) {
        $dbh->beginTransaction();
        $dbh->exec("UPDATE `id` SET `product_id` = {$n}, `data_index` = 'Some lond{$n} text{$n} with many{$n} unique{$n} words' WHERE `product_id` = {$n}");
        $dbh->commit();
    }
}

test_sample.php

<?php
/**
 * Copyright © 2015 Magento. All rights reserved.
 * See COPYING.txt for license details.
 */

require __DIR__ . '/db.php';

$dbh->beginTransaction();
for ($i = 2000000 + 1; $i <= 3000000; $i++) {
    $dbh->exec("INSERT INTO `sample` VALUES({$i}, 'Some lond{$i} text{$i} with many{$i} unique{$i} words')");
}
$dbh->commit();