3

For example, if i have these records

word

AAA

AAB

AAC

BAA AA

With a normal table i would use sql like

select * from table where word like 'AA%'order by H collate nocase asc

How do i select with FTS3 table instead? Also i would like to know if FTS3 will still have better performance than normal table with this kind of query?

Eddi
  • 479
  • 5
  • 22

1 Answers1

2

How do i select with FTS3 table instead?

Quoting the documentation:

An FTS table may be queried for all documents that contain a specified term (the simple case described above), or for all documents that contain a term with a specified prefix. As we have seen, the query expression for a specific term is simply the term itself. The query expression used to search for a term prefix is the prefix itself with a '*' character appended to it.

The documentation also gives a sample:

-- Query for all documents containing a term with the prefix "lin". This will match
-- all documents that contain "linux", but also those that contain terms "linear",
--"linker", "linguistic" and so on.
SELECT * FROM docs WHERE docs MATCH 'lin*';
CommonsWare
  • 986,068
  • 189
  • 2,389
  • 2,491
  • Hopefully, `MATCH '*ux'` (ends with) and `MATCH '*in*'` (contains) are supported too, as of FTS**3**? – Phantômaxx Dec 09 '14 at 12:34
  • 1
    @DerGolem The documentation mentions only prefix searches; suffixes are not supported. – CL. Dec 09 '14 at 12:51
  • 1
    @DerGolem: "contains" is simply standard `MATCH` syntax sans wildcards. You may wish to examine the documentation that I linked to for samples. But, as CL noted, the question is inquiring about prefixes. – CommonsWare Dec 09 '14 at 12:53
  • WTF! So, unfortunately, I can't use FTS3, since I need **all cases**; suffixes, prefixes and "in the middle"... – Phantômaxx Dec 09 '14 at 12:53
  • 2
    I have added a new record in the example. select * from table where word match 'AA*'order by H collate nocase asc will return result with BAA AA which is not what i want. If i use like 'AA%' result will be correct though. – Eddi Dec 09 '14 at 12:53
  • @Eddi: The prefix support in FTS3 is by *term*, not by *string*. You use FTS3 for full-text indexing of large prose, where you are searching for terms in that prose. – CommonsWare Dec 09 '14 at 12:55
  • Is that means FTS3 cannot provide the kind of search result i want and that i should just use a normal table? – Eddi Dec 09 '14 at 13:14
  • @Eddi: "Is that means FTS3 cannot provide the kind of search result i want" -- apparently. "and that i should just use a normal table?" -- apparently. With luck, the proper index will avoid your `LIKE` from doing a table scan. – CommonsWare Dec 09 '14 at 13:17