Two queries to retrieve user profile and tweets, or join and get user data from first column?

Question

Let's say I have two tables: users and tweets. I want to display a full user profile, and latest tweets by id.

Is it better to fetch them in two separate, possibly concurrent/parallel calls? in JS it would be:

const result = await Promise.all(
    query(`SELECT * FROM users WHERE id = 1`),
    query(`SELECT * FROM tweets WHERE user_id = 1`)
);
const profile = result[0];
const tweets = result[1];

OR

make a one query, simple join and retrieve user data from let's say first element of the array? e.g.

// pseudo code
const tweets = await query(`
SELECT * FROM users
JOIN tweets
ON users.id = tweets.id
WHERE users.id = 1`)

if (tweets.length > 0) {
    const { username, avatar } = tweets[0]; // reading any column to get user data
}

Is second query just wasting bandwidth because the user data is copied over every column?

Somehow it stuck with me that it's bad practice to make multiple sql calls and even in such a simple scenario I don't know whether I am overcomplicating things.

Answer 1

Two queries to retrieve user profile and tweets, or join and get user data from first column?

Is it better to fetch them in two separate, possibly concurrent/parallel calls?

OR

make a one query, simple join...

If you planned to join the data together outside of the database anyway, then typically it's more efficient to let the database do the join as databases are rather good at determining the best way to process data manipulations.

Conversely, if you planned to keep the data separate on the application, then it may be faster (from the application's perspective) to run two queries in parallel. Especially if you designed the UI of the application to display the data asynchronously, so part of the page starts loading while waiting on the rest of the data.

Your users table likely doesn't have many and large data type fields, so the bandwidth is probably mostly negligible in this specific scenario.

It sounds like your data will be kept separate from each other on the User Profile / Tweets page, so the latter design might be slightly better, especially for a User with many Tweets. But if you start implementing other pages and functionality where you'd need to join the data together (such as a CSV export that repeats the User info with each Tweet row) then it may make sense from a performance perspective or even just a simplified code perspective to do the join in the database layer.