YappoLogs

MyISAM/InnoDB で primary key/index/uniqu な複合カラムなインデックス貼って SELECT COUNT(*) FROM table WHERE column = x したベンチマーク

とってみた物の、どれも速度変わらんっぽいなぁ。計りかたわるいかなぁ。

結果は下記の通り
先頭の文字が l = InnoDB, h = MyISAM, 二番目の数字や、最後の文字の意味はベンチスクリプト見てくださいなり。

一応適すとでもこぴえp

[~/perl]$ perl ./sql-count-benchmark.pl
^[[C        Rate l4_i l3_i h2_n l3_n l2_i l4_n h3_n h4_n h1_i l1_n h3_i l1_i h4_i h1_n l2_n h2_i
l4_i 10638/s   --  -3%  -5%  -6%  -7% -10% -10% -11% -12% -13% -14% -14% -16% -17% -18% -18%
l3_i 10989/s   3%   --  -2%  -3%  -4%  -7%  -7%  -8%  -9% -10% -11% -11% -13% -14% -15% -15%
h2_n 11236/s   6%   2%   --  -1%  -2%  -4%  -4%  -6%  -7%  -8%  -9%  -9% -11% -12% -13% -13%
l3_n 11364/s   7%   3%   1%   --  -1%  -3%  -3%  -5%  -6%  -7%  -8%  -8% -10% -11% -12% -12%
l2_i 11494/s   8%   5%   2%   1%   --  -2%  -2%  -3%  -5%  -6%  -7%  -7%  -9% -10% -11% -11%
l4_n 11765/s  11%   7%   5%   4%   2%   --  -0%  -1%  -2%  -4%  -5%  -5%  -7%  -8%  -9%  -9%
h3_n 11765/s  11%   7%   5%   4%   2%   0%   --  -1%  -2%  -4%  -5%  -5%  -7%  -8%  -9%  -9%
h4_n 11905/s  12%   8%   6%   5%   4%   1%   1%   --  -1%  -2%  -4%  -4%  -6%  -7%  -8%  -8%
h1_i 12048/s  13%  10%   7%   6%   5%   2%   2%   1%   --  -1%  -2%  -2%  -5%  -6%  -7%  -7%
l1_n 12195/s  15%  11%   9%   7%   6%   4%   4%   2%   1%   --  -1%  -1%  -4%  -5%  -6%  -6%
h3_i 12346/s  16%  12%  10%   9%   7%   5%   5%   4%   2%   1%   --   0%  -2%  -4%  -5%  -5%
l1_i 12346/s  16%  12%  10%   9%   7%   5%   5%   4%   2%   1%   0%   --  -2%  -4%  -5%  -5%
h4_i 12658/s  19%  15%  13%  11%  10%   8%   8%   6%   5%   4%   3%   3%   --  -1%  -3%  -3%
h1_n 12821/s  21%  17%  14%  13%  12%   9%   9%   8%   6%   5%   4%   4%   1%   --  -1%  -1%
l2_n 12987/s  22%  18%  16%  14%  13%  10%  10%   9%   8%   6%   5%   5%   3%   1%   --  -0%
h2_i 12987/s  22%  18%  16%  14%  13%  10%  10%   9%   8%   6%   5%   5%   3%   1%   0%   --
[~/perl]$ perl ./sql-count-benchmark.pl
        Rate l3_i l4_n l3_n h4_n l4_i h4_i l2_n l2_i h2_n h2_i h1_i h3_i h3_n l1_i h1_n l1_n
l3_i 10753/s   --  -1%  -3%  -3%  -6%  -9% -10% -11% -11% -11% -11% -13% -14% -15% -16% -18%
l4_n 10870/s   1%   --  -2%  -2%  -5%  -8%  -9% -10% -10% -10% -10% -12% -13% -14% -15% -17%
l3_n 11111/s   3%   2%   --  -0%  -3%  -6%  -7%  -8%  -8%  -8%  -8% -10% -11% -12% -13% -16%
h4_n 11111/s   3%   2%   0%   --  -3%  -6%  -7%  -8%  -8%  -8%  -8% -10% -11% -12% -13% -16%
l4_i 11494/s   7%   6%   3%   3%   --  -2%  -3%  -5%  -5%  -5%  -5%  -7%  -8%  -9% -10% -13%
h4_i 11765/s   9%   8%   6%   6%   2%   --  -1%  -2%  -2%  -2%  -2%  -5%  -6%  -7%  -8% -11%
l2_n 11905/s  11%  10%   7%   7%   4%   1%   --  -1%  -1%  -1%  -1%  -4%  -5%  -6%  -7% -10%
l2_i 12048/s  12%  11%   8%   8%   5%   2%   1%   --   0%  -0%  -0%  -2%  -4%  -5%  -6%  -8%
h2_n 12048/s  12%  11%   8%   8%   5%   2%   1%   0%   --  -0%  -0%  -2%  -4%  -5%  -6%  -8%
h2_i 12048/s  12%  11%   8%   8%   5%   2%   1%   0%   0%   --   0%  -2%  -4%  -5%  -6%  -8%
h1_i 12048/s  12%  11%   8%   8%   5%   2%   1%   0%   0%   0%   --  -2%  -4%  -5%  -6%  -8%
h3_i 12346/s  15%  14%  11%  11%   7%   5%   4%   2%   2%   2%   2%   --  -1%  -2%  -4%  -6%
h3_n 12500/s  16%  15%  13%  13%   9%   6%   5%   4%   4%   4%   4%   1%   --  -1%  -2%  -5%
l1_i 12658/s  18%  16%  14%  14%  10%   8%   6%   5%   5%   5%   5%   3%   1%   --  -1%  -4%
h1_n 12821/s  19%  18%  15%  15%  12%   9%   8%   6%   6%   6%   6%   4%   3%   1%   --  -3%
l1_n 13158/s  22%  21%  18%  18%  14%  12%  11%   9%   9%   9%   9%   7%   5%   4%   3%   --

Server version: 5.1.33 Source distribution

use strict;
use warnings;
use DBI;

use Benchmark ':all';

my $dbh = DBI->connect('DBI:mysql:test');
sub setup {
    for (1..4) {
        $dbh->do("DROP TABLE IF EXISTS hatena$_");
        $dbh->do("DROP TABLE IF EXISTS labs$_");
    }

    $dbh->do(q{
CREATE TABLE hatena1 (
    id    int unsigned,
    name  char(10),
    primary key(id, name),
    index(name, id)
) TYPE=MyISAM
});
    $dbh->do(q{
CREATE TABLE hatena2 (
    id    char(10),
    name  char(10),
    primary key(id, name),
    index(name, id)
) TYPE=MyISAM
});
    $dbh->do(q{
CREATE TABLE hatena3 (
    id    char(10),
    name  char(10),
    unique(id, name),
    index(name, id)
) TYPE=MyISAM
});
    $dbh->do(q{
CREATE TABLE hatena4 (
    id    char(10),
    name  char(10),
    index(id, name),
    index(name, id)
) TYPE=MyISAM
});

    $dbh->do(q{
CREATE TABLE labs1 (
    id    int unsigned,
    name  char(10),
    primary key(id, name),
    index(name, id)
) TYPE=InnoDB
});
    $dbh->do(q{
CREATE TABLE labs2 (
    id    char(10),
    name  char(10),
    primary key(id, name),
    index(name, id)
) TYPE=InnoDB
});
    $dbh->do(q{
CREATE TABLE labs3 (
    id    char(10),
    name  char(10),
    unique(id, name),
    index(name, id)
) TYPE=InnoDB
});
    $dbh->do(q{
CREATE TABLE labs4 (
    id    char(10),
    name  char(10),
    index(id, name),
    index(name, id)
) TYPE=InnoDB
});
}
setup if $ARGV[0]||'' eq 'setup';

sub apply_insert {
    my @buf = @_;
    for my $num (1..4) {
        for my $name (qw/ hatena labs /) {
            $dbh->do(sprintf "INSERT INTO $name$num (id, name) VALUES%s;", join(', ', @buf));
        }
    }
}

if ($ARGV[0]||'' eq 'setup') {
    my @buf;
    for my $id (1..1000) {
        for my $name (1..1000) {
            push @buf, "($id, $name)";
            if (@buf == 1000) {
                apply_insert(@buf);
                @buf = ();
            }
        }
    }
    if (@buf) {
        apply_insert(@buf);
    }
}

my $coderefs = {};
my $countcache = {};
for my $name (qw/ hatena labs /) {
    for my $num (1..4) {
        my $table = "$name$num";
        for my $column (qw/ id name /) {
            my $testname = join '', substr($name, 0, 1), $num, '_', substr($column, 0, 1);
            $coderefs->{$testname} = sub {
                my $sth = $dbh->prepare("SELECT COUNT(*) FROM $table WHERE $column = ?");
                $countcache->{$testname}++;
                $sth->execute($countcache->{$testname} % 1000);
            };
        }
    }
}

cmpthese( 10000 => $coderefs);

という生魚的エントリ。