🤨 What is N+1 problem?

#sql #beginners #database #mysql

💎 What is it?

Simply put, a lot of SQL query executes in loop

💎 For example

There are two tables in database

▼ country table

id	name
1	Japan
2	Poland

▼ food table

id	country_id	name
1	1	sushi
2	1	ramen
3	1	udon
4	2	pierogi
5	2	kotlet
6	2	ziemniaki

Get country name

(I'm going to use ruby code)

# SELECT * FROM country; Country.all.each do |country| put country.name # Japan # Poland end

Call another table in loop

# SELECT * FROM country; Country.all.each do |country| put country.name # Japan # Poland # SELECT * FROM food WHERE country_id=1; # SELECT * FROM food WHERE country_id=2; # 😫 😭 if country table had 200 records, 200 query would be executed!!! OMG country.foods.each do |food| put food.name # sushi # ramen # udon # pierogi # kotlet # ziemniaki end end

💎 How to solve it?

JOIN
Eager Load

JOIN

Integrate tables !

# SELECT * FROM country INNER JOIN food ON country.id=food.country_id # 😀 😀 only 1 query ! Country.join(:food).all.each do |country| country.foods.each do |food| end end

Eager Load

Not integrate tables completely like JOIN, but executes SELECT at first !

# SELECT * FROM country; # SELECT * FROM food WHERE country_id IN(1,2) # 😀 😀 only 2 queries ! Country.includes(:food).all.each do |country| country.foods.each do |food| end end