Joining a Virtual Table (FTS) and a regular Table takes a lot of time to execute

[gholias]

I'm trying to join query between my Cars regular Table and my Makers Virtual Table for FTS

This is the makers Virtual Table

let markersTable = VirtualTable("makers")
let idColumn = Expression<String>("id")
let name = Expression<String>("name")
let text = Expression<String>("text")
let code = Expression<String>("code")

let config = FTS5Config()
      .column(idColumn)
      .column(name, [.unindexed])
      .column(code)
      .column(text)

and this is the Cars regular table

let carsTable = Table("Cars")
let idColumn = Expression<String>("id")
let name = Expression<String>("name")
let description = Expression<String>("description")
let makersCode = Expression<String>("makersCode")

The tables can be joined by

let query = carsTable
      .limit(1)
      .join(markersTable, on: makersCode == markersTable[code])

This works but takes a long time and I'm wondering what is the best way to make this join be faster?

I did some tests before using the FTS Virtual Table and if both tables are regular tables, the join is a lot faster.

Any help is appreciated.

[nathanfallet]

Try to add an index on code and markersCode. It will help SQLite to find data faster. Also, please close the issue if it solves your problem.