Optimal way to join three tables in SQLite

Question

A (simplified) database of Internet bookmarks. I thought it'd make sense to organize the tables logically, like so:

Bookmarks (id, title, url; basically external data)
+------+------------+-----+
| suid |   Title    | ... |
+------+------------+-----+

User (user-specific data: favorites, ratings, etc)
+------+------------+-----+
| suid | IsFavorite | ... |
+      +  (0 or 1)  +     +
+------+------------+-----+

History (last used, use count etc)
+------+------------+-----+
| suid |  LastUsed  | ... |
+      +(TDateTime) +     +
+------+------------+-----+

('suid' is unique ID, integer primary key)

From bookmarks flagged as favorite I need to select N most recently used (to populate a menu at runtime as a convenience).

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

The statement works, and seems sufficiently readable, but is it optimal? The Bookmarks table is intended to hold 20-50k records on average (i.e, not your standard browser bookmark manager :-) The app will execute 3 or 4 similar statements at startup to populate controls. All fields used in the example are indexed.

I'm teaching myself SQL and came up with the above code, but maybe I'm overlooking a syntax or an idiom that could improve it?

Answer 1

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.