IN is faster than JOIN
Date 2006-06-04 04:32:00 | Category: PHP
|
 
MySQL is one of the fastest Database Engine with simple queries, though it is not so fast on with complex queries.
Thus, we should divide queries simple as possible.
eg) permission system between categories and groups:
CREATE TABLE cat (
`cid` int NOT NULL default 0,
`name` varchar(255) NOT NULL default '',
PRIMARY KEY (cid)
) TYPE=MyISAM ;
CREATE TABLE cat_perm (
`cid` int NOT NULL default 0,
`gid` int NOT NULL default 0,
PRIMARY KEY (cid,gid)
) TYPE=MyISAM ;
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 ;
(1) A complex query using JOIN ...
SELECT item.* FROM item LEFT JOIN cat_perm ON cat_perm.cid=item.cid WHERE cat_perm.gid=1;
(2) two simple queries using IN ...
SELECT cid FROM cat_perm WHERE gid=1;
make cid list from the result, then
SELECT * FROM item WHERE cid IN (cid list);
(2) is faster than (1)
This is a result of just a simple bench mark.
In real, as permission system is more complex than it in real, (2) will be much faster than (1)
IN than JOIN
It sounds a good slogan 
------------------------------------
(2006/06/06 added)
Though the result of bench mark is correct, this is not a good comparison.
see also:
http://www.peak.ne.jp/xoops/md/news/article.php?storyid=123
|
|