SQLite FTS3 inconsitent performance

Question

A table with ~100k rows.

SELECT word FROM entries WHERE word MATCH '"chicken *"';
17 results in 46ms
SELECT word FROM entries WHERE word MATCH '"chicken f*"';
2 results in 5793ms

Why such a huge drop?

Answer 1

The wildcard in "chicken *" can effectively be ignored as it matches any token at all. The search is a simple lookup in the reverse index.

The wildcard in "chicken f*" needs to find all entries with words beginning with f, that also contain the word chicken. It is understandably more complicated and slower.