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