This is not a subjective question. I'd like to know objectively, what is the fastest at searching both:
- "search string" + wildcard
- wildcard + "search string" + wildcard
Constraints:
- Limit to first 20 results (1 preferred over 2, aka run "search string" + wildcard before wc+ss+wc and only if 20 results not found for first)
- 100,000 rows in db (non-changing)
This database would be very heavily used for searching only. Trying to switch from MySQL to a single file that can be implemented more easily in an executable or towards mobile development. If MySQL servers can be packaged that would work too, but from what I can tell that is a whole complex mess of it's own.
Do these features exist in somewhat reasonable performance in any database not requiring a server? So far we are working with key value store and trying to create multiple databases to parse search queries, but obviously that takes up a lot of hard drive space.
With MySQL our queries are max 0.05s (best 0.03s) using an index. With Go's BoltDB Key=>Value we're hitting max of 3.5s (as it has to query every single key to check for a match) -- so if there is a way to decrease this greatly we're really interested in finding out!
