Stack Overflow
Stack Overflow

Questions tagged [fts3]

FTS3 and FTS4 are SQLite virtual table modules that allow users to perform full-text searches on a set of documents.

The most common (and effective) way to describe full-text searches is "what Google, Yahoo and Altavista do with documents placed on the World Wide Web". Users input a term, or series of terms, perhaps connected by a binary operator or grouped together into a phrase, and the full-text query system finds the set of documents that best matches those terms considering the operators and groupings the user has specified.

135 questions
17
votes
1 answer

Get row ID of an SQLite FTS3 table

I have created an SQLite FTS3 table that I am using within the Android development platform (target is 2.2 and minVersion is 8). For example, I created the table as follows: create virtual table person using fts3(first,last); I know that when I…
jake
  • 1,405
  • 3
  • 19
  • 33
16
votes
1 answer

how can I get faster FTS4 query results ordered by a field in another table?

Background I'm implementing full-text search over a body of email messages stored in SQLite, making use of its fantastic built-in FTS4 engine. I'm getting some rather poor query performance, although not exactly where I would expect. Let's take a…
chazomaticus
  • 15,476
  • 4
  • 30
  • 31
14
votes
4 answers

ends with (suffix) and contains string search using MATCH in SQLite FTS

I am using SQLite FTS extension in my iOS application. It performs well but the problem is that it matches only string prefixes (or starts with keyword search). i.e. This works: SELECT FROM tablename WHERE columnname MATCH 'searchterm*' but…
Swapnil Luktuke
  • 10,385
  • 2
  • 35
  • 58
13
votes
4 answers

How to setup FTS3/FTS4 with python2.7 on Windows

FTS3/FTS4 doesn't work in python by default (up to 2.7). I get the error: sqlite3.OperationalError: no such module: fts3 or sqlite3.OperationalError: no such module: fts4 How can this be resolved?
Naveen
  • 5,910
  • 5
  • 30
  • 38
12
votes
2 answers

Using SQLite FTS3 with INTEGER columns

I'd like to use SQLite FTS3 (FTS4, actually) to index a table with integer columns, conceptually something like this: CREATE VIRTUAL TABLE whole (document INTEGER, page INTEGER, content TEXT, UNIQUE(document, page)) USING fts4(); I know that…
hatfinch
  • 3,095
  • 24
  • 35
12
votes
2 answers

How to implement fuzzy search with SQLite's FTS3?

I have already integrated search based on the official Android documentation and I'm using the following SQLite schema and query: CREATE VIRTUAL TABLE Search USING FTS3 ( _id, name, location ); select * from Search where name MATCH ? --…
TWiStErRob
  • 44,762
  • 26
  • 170
  • 254
12
votes
2 answers

SQLite fulltext virtual table normally usable?

Even after reading a lot about the fulltext index of SQLite and a question arises that I didn't see answered anywhere: I already have a table that I want to search with the fulltext index. I would just create an extra virtual table USING FTS3 or…
akirk
  • 6,757
  • 2
  • 34
  • 57
11
votes
5 answers

SQLite: Efficient substring search in large table

I'm developing an Android application that has to perform substring search in a large table (about 500'000 entries with street and location names, so just a few words per entry). CREATE TABLE Elements (elementID INTEGER, type INTEGER, name TEXT,…
Aletheios
  • 3,960
  • 2
  • 33
  • 46
10
votes
2 answers

Normal Table vs Virtual Table SQLite DB

Android developer manual seems to prefer FTS3 in SQLite DB when search is needed. I read the FTS3 description and it appears that it creates a virtual table instead of a permanent table. What's the difference between virtual table (FTS3) and normal…
androidnerd
  • 199
  • 2
  • 13
9
votes
1 answer

how to convert existing sqlite database table to fts3 one?

I have normal sqlite database and want to use fts3. As mentioned in http://www.sqlite.org/fts3.html tutorial FTS table has to be created for using this search functionality. Is there any way to convert existing table to FTS table?
vaichidrewar
  • 9,251
  • 18
  • 72
  • 86
8
votes
2 answers

How to write contains query in SQLite fts3 fulltext search

I want to make fulltext search in my index table which is sqlite fts3. For example; the data set is { "David Luiz", "David Villa", "Diego Costa", "Diego Ribas", "Diego Milito","Gabriel Milito", } When I type "vid i" I want to get {"David Luiz",…
ibrahimyilmaz
  • 18,331
  • 13
  • 61
  • 80
8
votes
1 answer

iOS SQLite full text search example

I am trying to build an app that utilizes SQLite FTS. I found a very interesting post on how to do this just the way I want to. However, I am not a very experienced programmer at all and especially not in CoreData which is used in this post. Is…
infobug
  • 313
  • 5
  • 17
7
votes
1 answer

How do I instruct SQLAlchemy to create SQLite FTS3 tables on create_all()?

I would like SQLAlchemy to create an FTS3 table during .create_all(). What special options do I need to add so it knows to CREATE VIRTUAL TABLE ... USING FTS3(tokenizer=...)?
joeforker
  • 40,459
  • 37
  • 151
  • 246
7
votes
2 answers

How to exclude column from FTS3 table search

I have such table: CREATE VIRTUAL TABLE t USING FTS3(hidden, text1, text2) I would like user to be able to searh over 'text1' and 'text2' columns, so the query is SELECT docid FROM t WHERE t MATCH ? And possible requests are: SELECT docid FROM t…
alex2k8
  • 42,496
  • 57
  • 170
  • 221
7
votes
1 answer

How to use FTS3 in SQLite

I have table with almost 200k entries. When I tried search with LIKE, it was very slow. Now I decided to use FTS. So I created two indexes where search will be held. Then I created fts virtual table. `CREATE TABLE [search_eng] ( [id] INTEGER…
Joe Rakhimov
  • 4,713
  • 9
  • 51
  • 109
1
2 3
…
8 9