PEAK XOOPS - IN than JOIN (2) in englishin japanese

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

in englishin japanese
Marijuana suggested me "You should compare with IN and INNER JOIN instead of LEFT JOIN".
As his suggestion sounds quite reasonable, I've done benchmark again.

The result
(LEFT JOIN) << (IN) ~ (INNER JOIN)

Thus I still insist "IN than JOIN".
That's because, dividing queries into permission and main makes coding easy.


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!