Database Performance Development Tutorials, Guides & Insights

• 1 query for users.
• 1 query for all related posts.

Only load relationships you need:

The N+1 query problem happens when your application executes one query to retrieve a parent dataset, followed by multiple additional queries to fetch related data for each parent record.

Imagine you want to fetch a list of posts along with their authors:

For example, to create an index on the last_name column of a users table:

CREATE INDEX idx_last_name ON users(last_name);

The MySQL Performance Schema is a powerful tool for monitoring database performance. It provides a wealth of information about the execution of SQL statements, memory usage, and other performance-related data.

Performance Schema is enabled by default in MySQL 5.6 and later. To verify it's enabled, run the following query:

The output provides details like:

• Select Type: Type of query (simple, primary, subquery, etc.).
• Table: The table accessed by the query.
• Type: Type of access (e.g., index, ALL, ref, const).
• Possible Keys: Indexes considered by the optimizer.
• Key: Index used for the query.
• Rows: Estimated number of rows examined.
• Extra: Additional information, such as whether a temporary table or file sort is used.