PEAK XOOPS - JOINよりIN (2) in englishin japanese

Archive | RSS |
PHP
PHP : JOINよりIN (2)
Poster : GIJOE on 2006-06-06 18:10:55 (19082 reads)

in englishin japanese
「INと比較すべきは、LEFT JOIN(外部結合)ではなく、INNER JOIN(内部結合)だ」という指摘を、Marijuanaさんから受けました。
確かにその通りでしたので、あらためてベンチマークを行ってみました。

その結果、NULL処理が増えた場合のLEFT JOINが極端に遅いだけで、INNER JOINとINとの比較では、ほぼ同じくらいの速度性能であることが判明しました。しかも、これは極めてリーズナブルな結果と言えるでしょう。

ただ、速度的なメリットがなかったとしても、権限テーブルが複雑になればなるほど、それを途中で分解できることでコーディングしやすくなるメリットは十分にあるはずで、INを使う方が良い、という結論には違いがないと思います。


OS: Linux kernel 2.4.20
DB: MySQL 3.23.58 at localhost
PHP: PHP 5.1.2-cli

create table:


#!/usr/local/bin/php-cli
<?php

$conn = mysql_connect( 'localhost' , 'root' , '' ) ;
mysql_select_db( 'test' , $conn ) ;


mysql_query( "
DROP TABLE IF EXISTS cat
" , $conn ) ;

mysql_query( "
CREATE TABLE cat (
  `cid` int NOT NULL default 0,
  `name` varchar(255) NOT NULL default '',
  PRIMARY KEY (cid)
) TYPE=MyISAM
" , $conn ) ;


mysql_query( "
DROP TABLE IF EXISTS item
" , $conn ) ;

mysql_query( "
CREATE TABLE item (
  `id` int NOT NULL default 0,
  `cid` int NOT NULL default 0,
  `name` varchar(255) NOT NULL default '',
  PRIMARY KEY (id),
  KEY (cid)
) TYPE=MyISAM
" , $conn ) ;


mysql_query( "
DROP TABLE IF EXISTS cat_perm
" , $conn ) ;

mysql_query( "
CREATE TABLE cat_perm (
  `cid` int NOT NULL default 0,
  `gid` int NOT NULL default 0,
  PRIMARY KEY (cid,gid)
) TYPE=MyISAM
" , $conn ) ;

$max_c = 100 ;
$max_i = 10000 ;
$denomi = 10 ;

$cids = array() ;
for( $c = 0 ; $c < $max_c ; $c ++ ) {
	mysql_query( "
	INSERT INTO cat SET `cid`=$c,`name`=rand()
	" , $conn ) ;
	if( rand(1,$denomi) == 1 ) {
		mysql_query( "
		INSERT INTO cat_perm SET `cid`=$c,`gid`=1
		" , $conn ) ;
		$cids[] = $c ;
	}
}

for( $i = 0 ; $i < $max_i ; $i ++ ) {
	mysql_query( "
	INSERT INTO item SET `id`=$i,`cid`=rand()*$max_c
	" , $conn ) ;
}

?>


benchmark (based on Marijuana's script)

#!/usr/local/bin/php-cli
<?php

$sql1 = "SELECT item.* FROM item LEFT JOIN cat_perm ON cat_perm.cid = item.cid WHERE cat_perm.gid =1";
$sql2 = "SELECT item.* FROM item INNER JOIN cat_perm ON cat_perm.cid = item.cid WHERE cat_perm.gid =1";
$sql3 = "SELECT item.* FROM item, cat_perm WHERE cat_perm.cid = item.cid AND cat_perm.gid =1";
$sql4 = "SELECT cid FROM cat_perm WHERE gid =1";
$sql5 = "SELECT * FROM item WHERE cid IN (";

$conn = mysql_connect( 'localhost' , 'root' , '' ) ;
mysql_select_db( 'test' , $conn ) ;

//1
$START_TIME = microtime(true);
  $a[0] = array();
  $result = mysql_query($sql1, $conn);
  while($val = @mysql_fetch_assoc($result)) {
    $a[0][] = $val;
  }
$end_time1 = sprintf("%01.04f",microtime(true)-$START_TIME);
$val = "";


//2
$START_TIME = microtime(true);
  $a[1] = array();
  $result = mysql_query($sql2, $conn);
  while($val = @mysql_fetch_assoc($result)) {
    $a[1][] = $val;
  }
$end_time2 = sprintf("%01.04f",microtime(true)-$START_TIME);
$val = "";


//3
$START_TIME = microtime(true);
  $a[2] = array();
  $result = mysql_query($sql3, $conn);
  while($val = @mysql_fetch_assoc($result)) {
    $a[2][] = $val;
  }
$end_time3 = sprintf("%01.04f",microtime(true)-$START_TIME);
$val = "";

//4
$START_TIME = microtime(true);
  $a[3] = array();
  $result = mysql_query($sql4, $conn);
  while(list($v) = @mysql_fetch_row($result)) {
    $in[] = $v;
  }
  $sql = $sql5.join(",",$in).")";
  $result = mysql_query($sql, $conn);
  while($val = @mysql_fetch_assoc($result)) {
    $a[3][] = $val;
  }
$end_time4 = sprintf("%01.04f",microtime(true)-$START_TIME);
$val = "";

echo "1:".$end_time1."\n";
echo "2:".$end_time2."\n";
echo "3:".$end_time3."\n";
echo "4:".$end_time4."\n";

// confirm
var_dump( array_diff( $a[0],$a[1] ) ) ;
var_dump( array_diff( $a[0],$a[2] ) ) ;
var_dump( array_diff( $a[0],$a[3] ) ) ;

?>


category:100 - item:10000 - probability: 1/10

1:0.0726 (LEFT)
2:0.0076 (INNER)
3:0.0076 (INNER2)
4:0.0079 (IN)


category:100 - item:10000 - probability: 1/2

1:0.0863 (LEFT)
2:0.0712 (INNER)
3:0.0712 (INNER2)
4:0.0650 (IN)

0 comments

Related articles
Printer friendly page Send this story to a friend

Comments list

Login
Username or e-mail:

Password:

Remember Me

Lost Password?

Register now!