#!/usr/bin/perl
use strict;
use warnings;
use Time::HiRes qw/gettimeofday tv_interval/;
use Benchmark qw/timethese cmpthese :hireswallclock/;
use DBI;
use Encode qw/encode decode/;
use Text::Ngram::MySQL::FullText;

our $VERBOSE = 1; # print in-depth benchmark

our $DBH = connect_db(
	db   => 'my_db',
	user => 'my_id',
	pass => 'my_pass'
);

our @QUERIES = (
	'銀座',
	'ダイニング',
	'銀座 タパス',
	'ダイニング 銀座',
	'銀座 タパス イタリアン',
	'餃子 紅虎',
);

our $PARSER = Text::Ngram::MySQL::FullText->new(
	column_name => 'searchText' );

sub like {
	print "\n" if $VERBOSE;
	foreach (@QUERIES){
		my @qs;
		my @text = split / /,$_;
		foreach my $text (@text){
			push @qs, qq{searchText like '%$text%'};
		}
		my $where = join ' AND ', @qs;
		my $t0 = [gettimeofday];
		my $res = $DBH->selectrow_hashref(qq{
			SELECT count(*) as cnt FROM shops
			WHERE $where ORDER BY name
		});
		next unless $VERBOSE;
		print '  ' . tv_interval( $t0 )
		. " sec / $res->{cnt} 件 / $_\n";
	}
	print "\n" if $VERBOSE;
}

sub fulltext {
	print "\n" if $VERBOSE;
	foreach (@QUERIES){
		my $match = $PARSER->to_match_sql( $_ );
		my $t0 = [gettimeofday];
		my $res = $DBH->selectrow_hashref(qq{
			SELECT count(*) as cnt FROM shops_fulltext
			WHERE $match ORDER BY name
		});
		next unless $VERBOSE;
		print '  ' . tv_interval( $t0 )
		. " sec / $res->{cnt} 件 / $_\n";
	}
	print "\n" if $VERBOSE;
}

timethese(1,{
	like => 'like',
	fulltext => 'fulltext',
});


sub connect_db {
	my %arg = @_;
	my($d,$db,$h) = ('mysql', $arg{db}, 'localhost');
	my $dsn = "DBI:$d:database=$db;host=$h";
	my $dbh = DBI->connect(
		$dsn,
		$arg{user},
		$arg{pass},
		{
			AutoCommit => 0,
			RaiseError => 1,
		}
	);
	$dbh->do("SET NAMES utf8");
	return $dbh;
}