Disco Plumber (74.510% quality rating):
Well, I'm no DBA, but I wouldn't expect a LEFT JOIN with an ON clause to
be that much worse than an INNER JOIN with a WHERE clause equating to
roughly the same thing. Is there an order of magnitude difference?
The only information I found regarding this in MySQL's docs was:
http://www.mysql.com/doc/en/LEFT_JOIN_optimisation.html
which implies that LEFT JOINS have extra optimization done.
Regardless, the database implementation should not be my concern as a
PHP programmer. If I am doing valid SQL queries with fairly sound logic
(i.e., putting the processing in the right places, not making
unnecessary amounts of queries), the underlying implementation of one
JOIN versus another should be irrelevant to me. Of course, if I am
querying for information I'm not going to use (e.g., if I was going to
ignore those rows of the result where fields came back NULL), that is a
waste of processing.
But anyway I decided to write a script to do benchmarks for myself...
And the results are almost random (load dependent). There's no clear
winner. Of course, MySQL may be doing caching. But then, MySQL would be
doing caching for the actual service as well.
For reference:
$ mysql --version
mysql Ver 11.16 Distrib 3.23.49, for pc-linux-gnu (i686)
Check out a handful of the test results:
500 left joins (on): 1.480120 sec
500 inner joins: 0.505901 sec
500 left joins (using): 0.549247 sec
500 left joins (on): 1.678084 sec
500 inner joins: 0.723299 sec
500 left joins (using): 0.877342 sec
500 left joins (on): 0.488853 sec
500 inner joins: 0.696226 sec
500 left joins (using): 0.481876 sec
1000 left joins (on): 0.975070 sec
1000 inner joins: 1.654450 sec
1000 left joins (using): 0.969804 sec
1000 left joins (on): 0.993082 sec
1000 inner joins: 1.018203 sec
1000 left joins (using): 1.094612 sec
10000 left joins (on): 10.364384 sec
10000 inner joins: 11.173975 sec
10000 left joins (using): 13.425231 sec
10000 left joins (on): 11.104748 sec
10000 inner joins: 13.026637 sec
10000 left joins (using): 9.970058 sec
10000 left joins (on): 10.662058 sec
10000 inner joins: 10.493683 sec
10000 left joins (using): 16.690147 sec
Here's the script I used:
#!/usr/bin/php4 -q
<?php
include("common .php"); // database connection
define('ITERATI ONS', 10000);
function diff($start, $end) {
list($stu, $sts) = explode(" ", $start);
$start = (float)$stu + (float)$sts;
list($etu, $ets) = explode(" ", $end);
$end = (float)$etu + (float)$ets;
return (float)($end - $start);
}
function bench($query, $desc) {
$start = microtime();
for($i=0;$i<ITE RATIONS;$i++)
mysql_query($qu ery);
$end = microtime();
$diff = diff($start, $end);
printf("%d %s: %f sec\n", ITERATIONS, $desc, $diff);
}
bench("SELECT * FROM users LEFT JOIN user_settings
ON users.uid = user_settings.u id
WHERE uname = 'phatjoe'",
"left joins (on)");
bench("SELECT * FROM users,user_sett ings
WHERE users.uid = user_settings.u id
AND uname = 'phatjoe'",
"inner joins");
bench("SELECT * FROM users LEFT JOIN user_settings
USING (uid)
WHERE uname = 'phatjoe'",
"left joins (using)");
?>
/joe
--
A dead relative's processor from the 118 will go to Myke.
