Fetch rows and associate them to their source filter without massive slowdowns

[RustyNova016]

Here's an exemple scenario:
I got 3 tables. "Players", "Teams", "Country".
A country has multiple teams, and a team has multiple players. I'm also using sqlite

I need, for each player, to get the country of their team.
I tried the naive approach to use a future::steam that map over each player, and does a query to get the country, but at large amounts of players (20000), this take ~6 minutes

How could I optimise it?
I tried to find a way with returning the country + player ID but SQLx doesn't support multiple results

[CommanderStorm]

Are you using an SQL join? If yes what are your Indexes like? 6 minutes for only 20k rows is a lot..

From your explanation, it seems like you are trying to do 20k database queries instead of one.

Select p.*, l.language
From players p, teams t, language l
Where p.team_id = t.id and l.id = t.language_id

Depending on your concrete semantics, you might also need to do a left/right join