SQL select in n to m relationship

Question

I have an n-to-m relationship between Author and Book.

Table Author

ID       Name
1        Follett  
2        Rowling
3        Martin

Table Book

ID     Title                       Category 
1        A Dance with Dragons      Fantasy
2        Harry Potter              Fantasy
3        The Key to Rebecca        Thriller
4        World without end         Drama

Table book_author

authorId       bookId
1        3  
2        2
3        1
1        4

There are a lot more authors and books in the system. Now I want to select all authors that have a book in genre "Fantasy".

This is what I came up so far with:

   select distinct a.id 
   from author a, book b, written w 
   where w.authorId = a.id and w.bookId = b.id and b.category = "Fantasy";

I am wondering how to optimize this query since especially table book is really large.

Answer 1

It is recommended to use an explicit JOIN instead of the implicit (comma-separated table list) join you currently have, as it will improve flexibility if and when you need to introduce left joins.

SELECT
  DISTINCT a.id
FROM
  author a
  JOIN book_author ba ON a.id = ba.authorId
  JOIN books b ON b.id = ba.bookId
WHERE b.category = 'Fantasy'

If your book_author has defined FOREIGN KEY relationships back to the author and books tables, indexes will be enforced. Likewise, the respective id columns in those tables should be defined as PRIMARY KEY. Beyond this, the only potential optimization you can do is to create an index on books.category.

CREATE TABLE book_author (
  authorId INT NOT NULL, /* or whatever the data type... */
  bookId INT NOT NULL,
  /* define FK constraints in book_author */
  FOREIGN KEY (authorId) REFERENCES author (id),
  FOREIGN KEY (bookId) REFERENCES books (id)
);