How to quickly search book titles?

Question

I have a database of about 200k books. I wish to give my users a way to quickly search a book by the title. Now, some titles might have prefix like A, THE, etc. and also can have numbers in the title, so search for 12 should match books with "12", "twelve" and "dozen" in the title. This will work via AJAX, so I need to make sure database query is really fast.

I assume that most of the users will try to search using some words of the title, so I'm thinking to split all the titles into words and create a separate database table which would map words to titles. However, I fear this might not give the best results. For example, the book title could be some 2 or 3 commonly used words, and I might get a list of books with longer titles that contain all 2-3 words and the one I'm looking for lost like a needle in a haystack. Also, searching for a book with many words in the title might slow down the query because of a lot of OR clauses.

Basically, I'm looking for a way to:

• find the results quickly
• sort them by relevance.

I assume this is not the first time someone needs something like this, and I'd hate to reinvent the wheel.

P.S. I'm currently using MySQL, but I could switch to anything else if needed.

Answer 1

Using a SOUNDEX is the best way i think.

SELECT
  id,
  title
FROM products AS p
WHERE p.title SOUNDS LIKE 'Shaw'

// This will match 'Saw' etc.

For best database performances you can best calculate the SOUNDEX value of your titles and put this in a new column. You can calculate the soundex with SOUNDEX('Hello').

Example usage:

UPDATE `books` SET `soundex_title` = SOUNDEX(title);