When designing a reservation system in PHP, what are some common approaches for optimizing database queries and improving performance when checking for overlapping reservations?

When checking for overlapping reservations in a reservation system, one common approach for optimizing database queries and improving performance is to use SQL queries that leverage indexes and avoid unnecessary data retrieval. One way to do this is by using a SQL query that checks for overlapping reservations based on the start and end times of the reservations, rather than retrieving all reservations and performing the check in PHP code.

// Assuming $start and $end are the start and end times of the new reservation being checked
$query = "SELECT COUNT(*) FROM reservations 
          WHERE (start_time < :end_time AND end_time > :start_time)";

$stmt = $pdo->prepare($query);
$stmt->execute(['start_time' => $start, 'end_time' => $end]);

$overlapCount = $stmt->fetchColumn();

if ($overlapCount > 0) {
    // Handle overlapping reservations
} else {
    // Proceed with creating the new reservation
}