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.

zerkms · Accepted Answer · 2010-10-15 05:51:23Z

2

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

answered Oct 15, 2010 at 5:51

zerkms

256k73 gold badges447 silver badges551 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

mouad · Accepted Answer · 2010-10-15 10:20:40Z

0

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;

answered Oct 15, 2010 at 10:20

mouad

70.5k18 gold badges117 silver badges106 bronze badges

Collectives™ on Stack Overflow

Efficient substring searching in Python with MySQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related