Choosing SQL in relationships n to m

Question

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

Table author

 ID Name 1 Follett 2 Rowling 3 Martin 

Table

 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 many more authors and books in the system. Now I want to select all authors who have a book in the Fantasy genre.

This is what I have come to so far:

  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 the book with books is really big.

Answer 1

It is recommended that you use an explicit JOIN instead of the implicit (comma-separated list of tables) that you have, as it will improve flexibility if and when you need to enter 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 defined the FOREIGN KEY relationship back to the author and books tables, the indexes will be respected. Similarly, the corresponding id columns in these tables should be defined as PRIMARY KEY . Also, the only potential optimization you can do is 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) );