sql - Swift 中的 SQLite3 查询很慢

Question

以下 SQLite3 语句在我的 Swift-Code 中非常慢（见下文）

知道为什么吗？

目前，从断点 1 到断点 2 大约需要 30 秒

// .. breakpoint-1
while ((sqlite3_step(statement) == SQLITE_ROW) {
   // .. breakpoint-2
}

我的 SQLite-db 大小为 450 MB，查询非常复杂。我只是不确定这个“while循环”是否不能以某种方式更快？

这是查询：

var query1: String = ""

query1 =
"""
SELECT DISTINCT st.departure_time as time, t.trip_headsign, r.route_desc, t.trip_id
FROM stops s
INNER JOIN calendar c ON t.service_id = c.service_id
INNER JOIN stop_times st  ON st.stop_id = s.stop_id
INNER JOIN trips t ON t.trip_id = st.trip_id
INNER JOIN routes r ON r.route_id = t.route_id
WHERE c.\(weekDay) = 1
AND st.departure_time > "\(departureTime)"
AND st.stop_id LIKE '\(stop_id)%'
AND s.stop_name <> t.trip_headsign
ORDER BY st.departure_time ASC
LIMIT 80
"""

此外，在maddy的输入之后，我确实创建了一个新数据库 - 这次是“索引”（请参阅​​下面的哪些列..）。索引后 - 数据库的大小增加到原来的两倍。

但是对于上述查询和新索引的数据库，不幸的是速度仍然很慢（> 30 秒）。拥有“索引”数据库后，我需要如何更改查询？？？我怎样才能提高查询速度？？？

这些是索引列：

CREATE INDEX departure_time_IDX ON stop_times(departure_time);
CREATE INDEX stop_times_id_IDX ON stop_times(stop_id);
CREATE INDEX stop_times_trip_id_IDX ON stop_times(trip_id);
CREATE INDEX stop_name_IDX ON stops(stop_name);
CREATE INDEX stop_id_IDX ON stops(stop_id);
CREATE INDEX trip_headsign_IDX ON trips(trip_headsign);
CREATE INDEX trip_id_IDX ON trips(trip_id);
CREATE INDEX trip_route_id_IDX ON trips(route_id);
CREATE INDEX service_id_IDX ON trips(service_id);
CREATE INDEX route_desc_IDX ON routes(route_desc);
CREATE INDEX route_id_IDX ON routes(route_id);
CREATE INDEX cal_service_id_IDX ON calendar(service_id);
CREATE INDEX monday_IDX ON calendar(monday);
CREATE INDEX tuesday_IDX ON calendar(tuesday);
CREATE INDEX wednesday_IDX ON calendar(wednesday);
CREATE INDEX thursday_IDX ON calendar(thursday);
CREATE INDEX friday_IDX ON calendar(friday);
CREATE INDEX saturday_IDX ON calendar(saturday);
CREATE INDEX sunday_IDX ON calendar(sunday);

这里是EXPLAIN QUERY PLAN

如果我消除 and 的 INNER JOIN，我的查询时间会缩短到 3trips秒routes。当然，我需要这两个来获得我期望的结果。但至少它表明这两个额外表的 INNER JOIN 正在减慢速度。

此外，必须注意的stop_times是，这是所有桌子中最大的！（然后来trips了——其他的都更小了……）

3 秒查询如下所示：

var faster_query: String = ""

faster_query =
"""
SELECT DISTINCT st.departure_time as time
FROM stops s
INNER JOIN stop_times st  ON st.stop_id = s.stop_id
WHERE st.departure_time > "19:51:00"
AND st.stop_id LIKE '8505000%'
ORDER BY st.departure_time ASC
LIMIT 80
"""

对此的解释查询计划faster_query如下所示：

这是 SQLite3 查询的完整代码

// Open SQLite database
var db: OpaquePointer? = nil
if let path = self.departure_filePath?.path {
    if sqlite3_open(path, &db) == SQLITE_OK {

        var statement: OpaquePointer? = nil
        // Run SELECT query from db
        if sqlite3_prepare_v2(db, query1, -1, &statement, nil) == SQLITE_OK {

            // Loop through all results from query1
            // .. breakpoint-1
            while ((sqlite3_step(statement) == SQLITE_ROW) {
                // .. breakpoint-2

                let ID = sqlite3_column_text(statement, 0)
                if ID != nil {
                    IDString = String(cString:ID!)
                } else {
                    print("ID not found", terminator: "")
                    return nil
                }
            }
        }
    }
}

标签： sqlswiftsqlite