PEAK XOOPS - IN than JOIN (2)

#!/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 ) ;
}

?>

#!/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] ) ) ;

?>