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();
	};
}
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();
	};
}

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

echo(" LIKE test
IN: $in_time
JOIN: $join_time
");

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

echo(" Test Indexed field
IN: $in_time
JOIN: $join_time
");