SQLite ORDER BY性能问题 [英] SQLite ORDER BY performance issue

问题描述

我有一个查询，有两个连接（连接三个表），运行速度相当快（通常小于1毫秒）。如果我根据三个表中任何一个表的索引列对结果进行排序，它仍然会在不到1毫秒的时间内运行。但是，当我使用两列（来自两个不同的表）的组合进行排序时，运行时间大约为240毫秒。

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?

我正在使用SQLite，这就是我的查询：

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;

推荐答案

无法使用索引加快排序速度来自两个不同表格的列。

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

这篇关于SQLite ORDER BY性能问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！