2 Select or 1 Join query?

Question

I have 2 tables:

book ( id, title, age ) ----> 100 milions of rows

author ( id, book_id, name, born ) ----> 10 millions of rows

Now, supposing I have a generic id of a book. I need to print this page:

Title: mybook

authors: Tom, Graham, Luis, Clarke, George

So... what is the best way to do this ?

1) Simple join like this:

Select book.title, author.name 
From book, author 
WHERE ( author.book_id = book.id ) AND ( book.id = 342 )

2) For avoid the join, I could make 2 simple query:

Select title FROM book WHERE id = 342

Select name FROM author WHERE book_id = 342 

What is the most efficient way ?

Answer 1

The first one. It's only a single round trip. It requires a little processing to collapse the rows of authors into a comma-separated list like you want but that's basically boilerplate code.

Separate related queries are a bad habit that will kill your performance faster than most things.

Answer 2

The best option is to run speed tests on your own server. Depending on how often the different tables are accessed together and apart, either one could be faster.

This has been answered in depth before: LEFT JOIN vs. multiple SELECT statements