I have a posts table and users table. And posts table has a column for author_id (foreign key). This is clearly an example of a One-To-Many relationship. I want to get all posts written by a specific user, say the one with ID 9.
That's my (naive) approach:
SELECT * FROM posts WHERE author_id = 9;
The problem with my naive approach is that I think it's very expensive for larger tables with more rows. Imagine if I have to go though all the comments posted on a social media platform just to get all the comments for that single post. That doesn't make sense!
It would be much better if I can access all posts related to a specific user by just accessing the users table. Is there any way to do that? If not, that's just a thought, so, are there any better approaches to my problem?
I use Sqlite3, but the question is not specific to a certain RDBMS, though I still want common and mainstream SQL, any answers with PL/SQL or some other more radical SQL won't be helpful for me.
Apply an index on author_id to make it faster and less expensive. Other approaches will be more expensive and complicated.