Tests UNIQUE index (char(80) and sha256 => int)

results.txt

 >>> Start test (1000 records)
Test time: 0.15334296226501 seconds, 0.37MB
Table: test
	Rows: 1000
	Data_length: 0.02 MB
	Index_length: 0.02 MB
Table: test_sha256
	Rows: 1000
	Data_length: 0.02 MB
	Index_length: 0.02 MB


 >>> Start test (10000 records)
Test time: 0.75374102592468 seconds, 0.37MB
Table: test
	Rows: 10000
	Data_length: 0.02 MB
	Index_length: 0.02 MB
Table: test_sha256
	Rows: 10000
	Data_length: 0.02 MB
	Index_length: 0.02 MB


 >>> Start test (100000 records)
Test time: 3.4877219200134 seconds, 0.37MB
Table: test
	Rows: 100000
	Data_length: 0.02 MB
	Index_length: 0.02 MB
Table: test_sha256
	Rows: 100000
	Data_length: 0.02 MB
	Index_length: 0.02 MB


 >>> Start test (1000000 records)
Test time: 32.141396045685 seconds, 0.37MB
Table: test
	Rows: 992550
	Data_length: 119.66 MB
	Index_length: 101 MB
Table: test_sha256
	Rows: 996051
	Data_length: 64.59 MB
	Index_length: 14.56 MB


 >>> Start test (10000000 records)
Test time: 332.14566802979 seconds, 0.37MB
Table: test
	Rows: 9929640
	Data_length: 1102 MB
	Index_length: 919 MB
Table: test_sha256
	Rows: 9924039
	Data_length: 1190 MB
	Index_length: 268 MB

test.php

<?php
/**
 * Created by IntelliJ IDEA.
 * User: funtik
 * Date: 013 13.12.16
 * Time: 19:27
 */

function addResults($text)
{
    file_put_contents(__DIR__ . '/result.txt', $text, FILE_APPEND);
}

function recreateTables(mysqli $mysqli)
{
    $mysqli->query('
        DROP TABLE `test`, `test_sha256`;
    ');
    $mysqli->query('
        CREATE TABLE `test` (
          `id` bigint(20) NOT NULL AUTO_INCREMENT,
          `text` char(80) NOT NULL,
          `adgroup_id` bigint(20) NOT NULL,
          PRIMARY KEY (`id`),
          UNIQUE KEY `test_adgroup_id_text_uindex` (`adgroup_id`,`text`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    ');
    $mysqli->query('
        CREATE TABLE `test_sha256` (
          `id` bigint(20) NOT NULL AUTO_INCREMENT,
          `text` char(80) NOT NULL,
          `adgroup_id` bigint(20) NOT NULL,
          `sha256_int` bigint(20) NOT NULL,
          PRIMARY KEY (`id`),
          UNIQUE KEY `test_adgroup_id_sha256_int_uindex` (`adgroup_id`,`sha256_int`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    ');
}

function startTest(mysqli $mysqli, $countRecords = 1000, $start = 0)
{
    $sqlTemplate = 'INSERT INTO `test` (`text`, `adgroup_id`) VALUES %s';
    $sqlTemplateSha256 = 'INSERT INTO `test_sha256` (`text`, `adgroup_id`, `sha256_int`) VALUES %s';
    $tmp = '';
    $tmpSha256 = '';
    for ($i = 0; $i < $countRecords; ++$i) {
        $keyword = sprintf('key%s key%s', $i, $i);
        $int = intval(substr(hash('sha256', $keyword), 0, 14), 16);

        $tmp .= sprintf(", ('%s', %d)", $keyword, $i);
        $tmpSha256 .= sprintf(", ('%s', %d, %d)", $keyword, $i, $int);

        if ($i % 500000 === 0) {
            $mysqli->query(sprintf($sqlTemplate, substr($tmp, 2)));
            $tmp = '';

            $mysqli->query(sprintf($sqlTemplateSha256, substr($tmpSha256, 2)));
            $tmpSha256 = '';

            echo 'i = ' . $i . "\n";
            echo 'Выполнения скрипта: '.(microtime(true) - $start).' сек., ' . (memory_get_usage() / 1024 / 1024) . "\n";
        }
    }
    if ($tmp) {
        $mysqli->query(sprintf($sqlTemplate, substr($tmp, 2)));
        $tmp = '';
    }
    if ($tmpSha256) {
        $mysqli->query(sprintf($sqlTemplateSha256, substr($tmpSha256, 2)));
        $tmpSha256 = '';
    }
}

function getStats(mysqli $mysqli)
{
    $sql = 'show table status from test';
    $s = '';
    foreach ($mysqli->query($sql) as $table) {
        $s .= "Table: " . $table['Name'] . "\n";
        $s .= "\tRows: " . $table['Rows'] . "\n";
        $s .= "\tData_length: " . round($table['Data_length'] / 1024 / 1024, 2) . " MB\n";
        $s .= "\tIndex_length: " . round($table['Index_length'] / 1024 / 1024, 2) . " MB\n";
    }
    return $s;
}

$start = microtime(true);


$mysqli = mysqli_connect('127.0.0.1', 'root', '', 'test', 3306);
$counts = [1000, 10000, 100000, 1000000, 10000000];
foreach ($counts as $countRecords) {
    addResults(" >>> Start test ($countRecords records)\n");
    recreateTables($mysqli);
    startTest($mysqli, $countRecords, $start);
    addResults('Test time: ' . (microtime(true) - $start) . " seconds, " . round(memory_get_usage() / 1024 / 1024, 2) . "MB\n");
    $stats = getStats($mysqli);
    addResults($stats . "\n\n");
}
$mysqli->close();


echo 'Время выполнения скрипта: '.(microtime(true) - $start).' сек.' . (memory_get_usage() / 1024 / 1024);