Efficient substring searching in Python with MySQL

Question

I'm trying to implement a live search for my website. One that identifies words, or parts of a word, in a given string. The instant results are then underlined where they match the query.

For example, a query of "Fried green tomatoes" would yield:

SELECT * 
FROM articles 
WHERE (title LIKE '%fried%' OR
       title LIKE '%green%' OR
       title LIKE '%tomatoes%)

This works perfectly with a very small dataset. However, once the number of records in the database increases, this query quickly becomes inefficient because it can't utilize indices.

I know this is technically what FULLTEXT searching in MySQL is for, but the quality of results just isn't as good.

What are some alternatives to get a very high quality substring search while keeping the query efficient?

Thanks.

Answer 1

Sphinx will help you to search fast within the huge amount of data

Answer 2

they are many FULLTEXT search engine that you can use like sphinx , Apache Solr, Whoosh (it's pure python) and Xapian. django-haystack (if you are using django) which can interface with the 3 last ones;