Optimal way to join three tables in SQLite

It is impossible (or at least very, very difficult) to guess from raw SQL exactly how the database engine will go about satisfying the query. For that reason you need to use EXPLAIN to find out how SQLite will actually get the data. And be aware that the execution plan it generates will be different depending on how much data is in each table and what the data "looks" like (in terms of number of different values found in indexed columns). So make sure to prime your test database with real-looking data.

Once you try EXPLAIN, I think there's a possibility that you'll find that SQLite is joining the tables together before taking the top 15 matches, which would be non-optimal if true. If that turns out to be the case, you might try something like:

SELECT Bookmarks.suid, Title from Bookmarks  INNER JOIN User USING (suid)  WHERE IsFavorite = 1  AND suid IN (SELECT suid FROM History ORDER BY LastUsed DESC LIMIT 15);

But, again, don't try that until you've seen from EXPLAIN that SQLite is getting the data in a non-optimal manner.

It looks ok to me. You might know this already but you might investigate your query using the explain keyword (http://www.sqlite.org/lang_explain.html) if you want to tune it further.

Cheers!

You can use the keyword EXPLAIN as already said, but there are some other ways to improve it.There is a website with some info... check it out clickin' here to find out more about it...good luck!