N+1 Query

N+1 Query is a Database Querying (anti) pattern. In this pattern, the client makes N + 1 SELECT calls to database to retrieve information.

Consider the following example. First we are retrieving multiple records from the Department table.

SELECT * from Department;

Now, for each record from the Department table, we are retrieving the Employee.

SELECT * from Employee WHERE dept_id = ?

This is N+1 Pattern. N is the Number of records returned by the first SELECT statement, plus 1 (the first statement itself). This pattern will cause significant loss in performance. And should be avoided.

This could be prevented by using some sort of caching mechanism. First, SELECT all the rows from Employee table. Store it in memory (or in Disk) using a caching Database such as Redis. Now instead of querying the actual database, make sure to use the in-memory Redis database. Also make sure that you invalidate cache as and when required.