How to implement a search engine using python mysql?

Question

I have a MySQL database created using a custom Python script. I need to implement full-text search on a table in the database. I can use SELECT * FROM myTable WHERE (title LIKE '%hello%' OR title LIKE '%world%'), however I don't think that is a very efficient way of implementing search since the data in the table has nearly one million rows.

I am using innoDB tables so the built in MySQL full text search for MyISAM will not work. Any suggestions on methods or tutorials that will point me in the right direction?

Answer 1

If your data is content like you could use some full-text search specific engine like Lucene:

http://lucene.apache.org/pylucene/

If you are doing Django you have Haystack:

http://haystacksearch.org/

Solr is also a full-text search related technology you might read about:

http://wiki.apache.org/solr/SolPython

Answer 2

I am no expert with MySQL, but I can immediately say that you should not be selecting everything that is like to a value. If the user types in "and", and there are thousands of results, it may be better just to select a certain amount from the database and then load more using the LIMIT parameter when the user goes to the next page (e.g).

SELECT * FROM `myTable` WHERE (`title` LIKE '%hello%' OR `title` LIKE '%world%') LIMIT numberOfValues,startingAtRowNumber

So to answer your question, the query is not efficient, and you should use something like I suggested above.

Answer 3

Take a look at: Fulltext Search with InnoDB. They suggest using an external search engine since there is no really good option to search within innoDB tables.