PHP 'smart' search engine to search Mysql

Question

I am creating a search engine for my php based website. I need to search a mysql table.

Thing is, the search engine must be pretty 'smart', so that users can easily find their items (it's a classifieds website).

I have currently set up a FULLTEXT search with this piece of code:

    MATCH (headline) AGAINST ($querystring)

But this isn't enough...

For instance, lets say the field headline contains something like Bmw 330ci. If I search for 330, I wont get any results. The ending ('ci') is just one of many endings in car models which must be taken into account when searching the table.

Or what if the headline field is bmw330? Also no results, because it only matches full words.

Or also, what if the headline is bmw 330, and I search for bmw 520, still with FULLTEXT I will get the bmw 330 as a result, even though I searched for bmw 520... Not good!

How should I solve this problem?

Answer 1

When it comes to fulltext search, people who want free solutions often tend to use either Sphinx or Solr.

I've not used any of those two, but I've read several times that they were great, and easy to use from/with PHP and MySQL.

Answer 2

Don't reinvent the wheel: inverted-index search engine are already there, free of charge, open source, easy and powerful. They have all what you need for such kind of search requirements.

Depending on your context, you can choose between a search library like Apache Lucene or a search platform like Apache Solr or Elastic Search.

All of them have a great documentation and they are widely used. That extremely minimizes the learning curve, even if you never worked with fulltext search world.