SQLite ORDER BY performance issue

问题:

I have a query with two joins (connecting three tables) that runs fairly fast (usually less than 1ms). If I sort the results based on an indexed column of any of the three tables, it still runs in less than 1 ms. However, when I use a combination of two columns (from two different tables) for sorting, the running time is around 240ms.

Do I need something like a composite index for columns from different tables? I assume this is not possible. Do I have to use indexed views to achieve a similar goal? Or, is there something wrong with my design?

I'm using SQLite, and this is what my query looks like:

SELECT image.title,
       project.title,
       video.title
FROM image
JOIN video ON image.video_id=video.id
JOIN project ON project.id=video.project_id
ORDER BY image.video_id,
         project.title LIMIT 5000;

回答1:

It is not possible to use indexes to speed up sorting by columns from two different tables.