JameSQL supports several query types, including and/or, range, span, and wildcard. It also has a text-based query language with support for operators that is transpiled back into a JSON query to be executed.
You can try it out at:
https://jamesg.blog/search-pages/?q=category_lower:%27indiew...
Under the hood, I use a reverse index for text and boolean searches, a b-tree for range queries (using the btrees Python package), and a trie for string prefix queries (using the pygtrie package). Most queries are executed in < 0.01s.