phimath phimath - 3 months ago 13
SQL Question

What determines search order in sqlite join?

I have two tables:

books:
id INTEGER PRIMARY KEY,
author TEXT,
title TEXT


and

favoriteBooks:
id INTEGER PRIMARY KEY,
FOREIGN KEY(id) REFERENCES books(id)


I am trying to see if an author exists in favorite books.

The following statment works, but is slow if favoriteBooks has a small number of rows and fast if there are a large number. I think its because I am searching through every row in books and seeing if it is in favoriteBooks instead of going through each favoriteBook and checking the author.

How can I fix this?

SELECT EXISTS (
SELECT 1 FROM books b
INNER JOIN favoriteBooks f ON b.id = f.id
AND author="Some Author" LIMIT 1);


thanks!

Answer Source

This query:

SELECT EXISTS (SELECT 1
               FROM books b INNER JOIN
                    favoriteBooks f
                    ON b.id = f.id AND author = 'Some Author'
               LIMIT 1
              );

is one way to write the logic. Note that the LIMIT is unnecessary. The EXISTS takes care of that.

You want an index on books(author, id):

CREATE INDEX idx_books_author_id ON books(author, id);

Note that there are essentially two ways to process this data. Go through all the books that have the specified author and then see which are favorite books. Or, go through all the favorite books and see which have the specified author.

Your question suggests that the number of favorite books can grow arbitrarily large, so the first method is better.

Also, note that a book can have multiple authors, but your question doesn't seem to take that into account.