andylibrian/fill-category.php

## fill-category.php
<?php

require __DIR__ . '/vendor/autoload.php';

$dsn = 'mysql:dbname=db1;host=127.0.0.1';
$user = 'root';
$password = '(disensor)';

try {
    $dbh = new PDO($dsn, $user, $password);
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}

$sth = $dbh->prepare('INSERT INTO category(category_name, category_description, category_enabled) VALUES(:category_name, :category_description, :category_enabled)');

$faker = Faker\Factory::create();
$faker->seed(time());

for ($i = 1; $i <= 1000000; $i++) {
        $sth->bindValue('category_name', $faker->words(4, true) . " $i");
        $sth->bindValue('category_description', $faker->text() . " $i");
        $sth->bindValue('category_enabled', mt_rand(0, 1));
        $sth->execute();
}

## fill-product.php
<?php

require __DIR__ . '/vendor/autoload.php';

$dsn = 'mysql:dbname=db1;host=127.0.0.1';
$user = 'root';
$password = '(disensor)';

try {
    $dbh = new PDO($dsn, $user, $password);
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}

$sth = $dbh->prepare('INSERT INTO product(product_name, product_description, product_enabled, product_category, product_price) VALUES(:product_name, :product_description, :product_enabled, :product_category, :product_price)');

$faker = Faker\Factory::create();
$faker->seed(time());

for ($i = 1; $i <= 50000000; $i++) {
        $sth->bindValue('product_name', $faker->words(8, true) . " $i");
        $sth->bindValue('product_description', $faker->text(600) . " $i");
        $sth->bindValue('product_enabled', mt_rand(0, 1));
        $sth->bindValue('product_category', mt_rand(40, 1000000));
        $sth->bindValue('product_price', mt_rand(50000, 500000));
        $sth->execute();
        if ($i % 100000 == 0) {
            echo "$i\n";
        }
}

## index.php
<?php


$dsn = 'mysql:dbname=db1;host=127.0.0.1';
$user = 'root';
$password = '(disensor)';

try {
    $dbh = new PDO($dsn, $user, $password);
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}

$afterId = (int)$_GET['after_id'];

$products = [];

$sql = "SELECT p.*, c.category_name from product p JOIN category c on p.product_category = c.category_id WHERE product_id > $afterId ORDER BY product_id ASC LIMIT 10";

$start = microtime(true);

foreach($dbh->query($sql, PDO::FETCH_ASSOC) as $product) {
        $products[] = $product;
}

$time_elapsed_secs = microtime(true) - $start;

foreach ($dbh->query('show table status where name = \'product\'') as $status) {
        break;
}
$productCount = $status['Rows'];

$explains = [];
foreach($dbh->query('EXPLAIN ' . $sql, PDO::FETCH_ASSOC) as $explain) {
        $explains[] = $explain;
}

?>
<!DOCTYPE html>
<html>
<head>
</head>
<body>

processing time: <?= $time_elapsed_secs ?> second.<br /><br />

<h2>Products</h2>
<table width="100%">
        <thead>
                <tr>
                        <th>product_id</th>
                        <th>product_name</th>
                        <th>product_category</th>
                        <th>category_name (join)</th>
                        <th>product_description</th>
                        <th>product_enabled</th>
                        <th>product_price</th>
                </tr>
        </thead>
        <tbody>
                <?php foreach ($products as $product): ?>
                <tr>
                        <td><?= htmlspecialchars($product['product_id']) ?></td>
                        <td><?= htmlspecialchars($product['product_name']) ?></td>
                        <td><?= htmlspecialchars($product['product_category']) ?></td>
                        <td><?= htmlspecialchars($product['category_name']) ?></td>
                        <td><?= substr(htmlspecialchars($product['product_description']), 0, 50) . ' ...' ?></td>
                        <td><?= htmlspecialchars($product['product_enabled']) ?></td>
                        <td><?= htmlspecialchars($product['product_price']) ?></td>
                </tr>
                <?php endforeach; ?>
        </tbody>
</table>
<br />
<a href="/">First Page</a> | <a href="?after_id=<?= $product['product_id'] ?>">Next</a>     <span>Total rows (estimated) : <?= $productCount ?></span>

<br /><br />

<div style="">
    <strong>Query executed:</strong><br />
    <pre>
        <?= htmlspecialchars($sql) ?>
    </pre>
    <strong>Explain:</strong><br />
    <table width="100%">
        <thead>
                <?php foreach (array_keys($explains[0]) as $explainKey): ?>
                <th><?= htmlspecialchars($explainKey) ?></th>
                <?php endforeach; ?>
        </thead>
        <tbody>
        <?php foreach ($explains as $explain): ?>
                <tr>
                        <?php foreach ($explain as $value): ?>
                        <td><?= htmlspecialchars($value) ?></td>
                        <?php endforeach; ?>
                </tr>
                <?php endforeach; ?>
        </tbody>
    </table>
</div>

</body>
</html>
	<?php

	require __DIR__ . '/vendor/autoload.php';

	$dsn = 'mysql:dbname=db1;host=127.0.0.1';
	$user = 'root';
	$password = '(disensor)';

	try {
	$dbh = new PDO($dsn, $user, $password);
	$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	} catch (PDOException $e) {
	echo 'Connection failed: ' . $e->getMessage();
	}

	$sth = $dbh->prepare('INSERT INTO category(category_name, category_description, category_enabled) VALUES(:category_name, :category_description, :category_enabled)');

	$faker = Faker\Factory::create();
	$faker->seed(time());

	for ($i = 1; $i <= 1000000; $i++) {
	$sth->bindValue('category_name', $faker->words(4, true) . " $i");
	$sth->bindValue('category_description', $faker->text() . " $i");
	$sth->bindValue('category_enabled', mt_rand(0, 1));
	$sth->execute();
	}