(Medium) JOIN vs WHERE IN vs N+1 benchmark

bench_join.php

<?php

/** @var PDO $pdo */
$pdo = require __DIR__ . '/common.php';

for ($n = 1; $n <= BENCHMARK_RECORDS; $n++) {
    $statement = $pdo->prepare(<<<SQL
        SELECT salaries.*, employees.*
        FROM salaries INNER JOIN employees ON employees.emp_no = salaries.emp_no
        LIMIT $n
SQL
    );

    benchmark($n, function() use ($statement) {
        $statement->execute();
        $statement->fetchAll(PDO::FETCH_ASSOC);
    });
}

bench_n+1.php

<?php

/** @var PDO $pdo */
$pdo = require __DIR__ . '/common.php';

for ($n = 1; $n <= BENCHMARK_RECORDS; $n++) {
    $selectSalaries = $pdo->prepare("SELECT * FROM salaries LIMIT $n");
    $selectEmployee = $pdo->prepare('SELECT * FROM employees WHERE emp_no = ?');

    benchmark($n, function() use ($selectSalaries, $selectEmployee) {
        $selectSalaries->execute();
        $salaries = $selectSalaries->fetchAll(PDO::FETCH_ASSOC);

        foreach ($salaries as $salary) {
            $selectEmployee->execute([$salary['emp_no']]);
            $selectEmployee->fetch(PDO::FETCH_ASSOC);
        }
    });
}

bench_none.php

<?php

/** @var PDO $pdo */
$pdo = require __DIR__ . '/common.php';

for ($n = 1; $n <= BENCHMARK_RECORDS; $n++) {
    $statement = $pdo->prepare("SELECT * FROM salaries LIMIT $n");

    benchmark($n, function() use ($statement) {
        $statement->execute();
        $statement->fetchAll(PDO::FETCH_ASSOC);
    });
}

bench_where_in.php

<?php

/** @var PDO $pdo */
$pdo = require __DIR__ . '/common.php';

for ($n = 1; $n <= BENCHMARK_RECORDS; $n++) {
    $selectSalaries = $pdo->prepare("SELECT * FROM salaries LIMIT $n");

    $placeholders = array_fill(0, $n, '?');
    $placeholders = implode(', ', $placeholders);

    // Note: this really tests the best case scenario: in a real world app, we probably wouldn't know the number of
    // records in advance and would have to create a custom prepared statement for each query.
    $selectEmployees = $pdo->prepare("SELECT * FROM employees WHERE emp_no IN($placeholders)");

    benchmark($n, function() use ($selectSalaries, $selectEmployees) {
        $selectSalaries->execute();
        $salaries = $selectSalaries->fetchAll(PDO::FETCH_ASSOC);

        $empnos = [];

        foreach ($salaries as $salary) {
            $empnos[] = $salary['emp_no'];
        }

        $selectEmployees->execute($empnos);
        $selectEmployees->fetchAll(PDO::FETCH_ASSOC);
    });
}

common.php

<?php

/**
 * The number of records to benchmark. We'll loop from 1 to this number.
 */
define('BENCHMARK_RECORDS', 50);

/**
 * The number of seconds to run each benchmark for.
 */
define('BENCHMARK_SECONDS', 10);

/**
 * The number of times a benchmark is repeated. The best result is used.
 */
define('BENCHMARK_REPEAT', 10);

/**
 * The number of best results to average. Must be between 1 and BENCHMARK_REPEAT.
 */
define('BENCHMARK_KEEP_BEST', 3);

/**
 * The benchmark function that will be called by each bench_*.php script.
 */
function benchmark(int $n, callable $function)
{
    $results = [];
    $seconds = (float) BENCHMARK_SECONDS;

    for ($i = 0; $i < BENCHMARK_REPEAT; $i++) {
        $t = microtime(true);
        $iterations = 0;

        do {
            $function();
            $iterations++;
        } while (microtime(true) - $t < $seconds);

        $results[] = $iterations;
    }

    rsort($results);

    $total = 0;

    for ($i = 0; $i < BENCHMARK_KEEP_BEST; $i++) {
        $total += $results[$i];
    }

    $average = intdiv($total, BENCHMARK_KEEP_BEST * BENCHMARK_SECONDS);

    echo "$n,$average\n";
}

/**
 * Change credentials as needed.
 */

return new PDO('mysql:host=localhost;dbname=employees', 'root', '', [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_EMULATE_PREPARES => false
]);

setup.sql

/**
 * To set up the employees database used for benchmarks:
 *
 * - Download https://github.com/datacharmer/test_db/archive/0b3c9796d9bf36ce34314aff99d217c23c609e31.zip
 * - Import the employees.sql file
 * - Import this setup.sql file
 */

/* Keep only one salary record per employee, to ensure that we JOIN n *distinct* records */

DELETE t1 FROM employees.salaries t1
INNER JOIN employees.salaries t2
WHERE t1.emp_no = t2.emp_no AND t1.from_date < t2.from_date;