How can PHP developers optimize database performance by using normalized tables and foreign key constraints for efficient data retrieval?

To optimize database performance, PHP developers can use normalized tables and foreign key constraints to efficiently retrieve data. By breaking down data into smaller, related tables and establishing relationships between them using foreign keys, queries can be executed more quickly and accurately. This approach helps reduce redundancy, improve data integrity, and enhance overall database performance.

// Example of creating normalized tables and foreign key constraints in MySQL using PHP

// Create users table
$query = "CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL
)";
mysqli_query($connection, $query);

// Create posts table with foreign key constraint
$query = "CREATE TABLE posts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(100) NOT NULL,
    content TEXT,
    user_id INT,
    FOREIGN KEY (user_id) REFERENCES users(id)
)";
mysqli_query($connection, $query);