gistfile1.txt

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

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

function test_eager($db, $query) {
	return function() use($db, $query) {
		$merged = $db->query("SELECT a.id as author_id, a.name as author_name, p.id as post_id, p.title as post_title from authors a 
				JOIN posts p ON p.author_id = a.id 
				where p.$query");
		$posts = [];
		foreach($merged as $merge) {
			$posts[]= [
					'id' => $merge['post_id'], 
					'title' => $merge['post_title'],
					'author' => [
							'id' => $merge['author_id'], 
							'name' => $merge['author_name'], 
						]
				];
		}
	};
	
	$authors = array_combine(array_column($authors, 'id'), $authors);
	foreach($posts as &$post) {
		$post['author'] = $authors[$post['author_id']];
	}
}



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

echo(" LIKE test
IN (2 Queries): $in_time
JOIN (2 Queries): $join_time
EAGER (1 Query): $eager_time
");

$in_time = benchmark(test_in($db, "published=1"));
$join_time = benchmark(test_join($db, "published=1"));
$eager_time = benchmark(test_eager($db, "published=1"));

echo(" Test Indexed field
IN (2 Queries): $in_time
JOIN (2 Queries): $join_time
EAGER JOIN (1 Query): $eager_time
");