I want to allow users to search a sqlite-backed music database. The search should include both, artist and song title, and should be word-based (search for one or more word-prefixes) and case-insensitive. The solution should only use plain SQL, as it needs to work with different language bindings.
For example: Searching for "hea" should find both, "Heart of Gold" and "Stairway to Heaven", but "day" won't find "Yesterday".
Combining search terms should find only entries that contain both word prefixes (e.g. "hea led" will only find "Stairway to Heaven" from Led Zeppelin).
So how do I build the database in order to perform efficient queries and how should the (language independent) queries look like?
I read about the like optimization of sqlite. I can perform efficient like queries, if there is an index on a column, and I query for a prefix. As I want to find all world of both, the title and the artist, I thought about a table with two columns: one for words, and one for the corresponding index of the music table. When building the search index I would extract all worlds from the artist and title and create a new row in the query table for it.
What do you think about this approach? Will it work? It would lead to much duplicate entries for both, common words and words of artist names. But I don't care much about disk-space efficiency, if it leads to a better performance.
When I search for a single word "prefix", I could create a query like
SELECT id from query_table where word like 'prefix%'
But how can I generate a reliable and fast query when I search for e.g. 5 word prefixes?