What are the potential performance issues of displaying all rows from a database table on a single PHP page?

Displaying all rows from a database table on a single PHP page can lead to performance issues due to the large amount of data being fetched and displayed at once. This can slow down the page load time and potentially crash the server if the dataset is too large. To solve this issue, pagination can be implemented to limit the number of rows displayed on each page, improving performance and user experience. 

<?php
// Connect to the database
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database";

$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Set the number of rows to display per page
$rows_per_page = 10;

// Determine the current page number
if (isset($_GET['page'])) {
    $page = $_GET['page'];
} else {
    $page = 1;
}

// Calculate the starting row for the query
$start = ($page - 1) * $rows_per_page;

// Fetch data from the database with pagination
$sql = "SELECT * FROM table_name LIMIT $start, $rows_per_page";
$result = $conn->query($sql);

// Display the data in a table
if ($result->num_rows > 0) {
    echo "<table>";
    // output data of each row
    while($row = $result->fetch_assoc()) {
        echo "<tr><td>" . $row["column1"]. "</td><td>" . $row["column2"]. "</td></tr>";
    }
    echo "</table>";
} else {
    echo "0 results";
}

// Pagination links
$sql = "SELECT COUNT(*) AS total FROM table_name";
$result = $conn->query($sql);
$row = $result->fetch_assoc();
$total_rows = $row['total'];
$total_pages = ceil($total_rows / $rows_per_page);

for ($i=1; $i<=$total_pages; $i++) {
    echo "<a href='?page=".$i."'>".$i."</a> ";
}

// Close the database connection
$conn->close();
?>

Keywords

MySQL pagination memory consumption query optimization server overload

Related Questions