join_vs_in.php

<?php
$db = new PDO('mysql:host=localhost;dbname=rmc2', 'root');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
function benchmark($callback){
  $start = microtime(true);
	for ($i = 0; $i < 100; $i++)
		$callback();
	return microtime(true) - $start;
}

function test_in($db, $query) {
	return function() use($db, $query) {
		$ids = [];
		$posts = [];
		foreach($db->query("SELECT * from post where $query LIMIT 100") as $post) {
			$posts[] = $post;
			$ids[] = $post['author_id'];
		}
		$authors = $db->query("SELECT id, username from user where id in (".implode(',', $ids).")")->fetchAll();
	};
}
function test_join($db, $query) {
	return function() use($db, $query) {
		$posts = $db->query("SELECT * from post where $query ")->fetchAll();
		$authors = $db->query("SELECT a.id, a.username from user a
				JOIN post p ON p.author_id = a.id
				where p.$query LIMIT 100")->fetchAll();
	};
}

$in_time = benchmark(test_in($db, "title like '%yii%'"));
$join_time = benchmark(test_join($db, "title like '%yii%'"));

echo "Like test:\n";
printf("IN: %f s\n", $in_time)."\n";
printf("JOIN: %f s\n", $join_time)."\n";
echo "\n\n";

$in_time = benchmark(test_in($db, "comment_count>5"));
$join_time = benchmark(test_join($db, "comment_count>5"));

echo "Field test:\n";
printf("IN: %f s\n", $in_time)."\n";
printf("JOIN: %f s\n", $join_time)."\n";
echo "\n\n";

Like test:
IN: 1.190254 s
JOIN: 1.752970 s

Field test:
IN: 0.879946 s
JOIN: 2.595336 s