[FIXED] What is a good database platform designed for partial string matching in Java?

Issue

I apologize if this question seems a bit general, but I've been Googling around to no avail and I'm not sure where else to go.

I've got a SQLite database in java that stores a lot of song title/artist names. Currently I'm querying the database like this:

SELECT * FROM music WHERE (title LIKE "%adio%" OR artist LIKE "%adio%") ORDER BY title, ASC LIMIT 20

Obviously this method is fairly slow because I use a wildcard on each side of the search term.

The thing is, if I search for "Radioactive" in iTunes, I can search "adio" and Radioactive pops up fairly quickly. What kind of database accomplishes this searching so quickly? I've looked into FTS in SQLite, but that doesn't allow for prefixed wildcards. I have a feeling SQLite isn't the correct choice here so I'm looking for suggestions. Thanks for your time!

Solution

Many relational databases have support for a feature called "Full Text Search" (FTS), which is what you are looking for.
FTS is a special kind of index that makes word searches fast. The syntax for FTS queries depends on the database (it is not standard like SQL). FTS indexes can be language specific which allows searches for similar words, synonyms, plural/singular in addition to exact word and prefix/suffix searches.
If you are looking for a non commercial option Postgres supports FTS.

But it really depends on your application. You might be better of with a non relational database. Take a look a Lucene for a stand alone full text search index, or at Elastic which is build on top of Lucene.

Most databases and certainly common ones can be accessed using Java, so that is not a concern when choosing a solution.

Answered By - Eli Algranti

Answer Checked By - Cary Denson (PHPFixing Admin)