Advice on searching with PHP and MySQL

views:

answers:

+2 Q:

Advice on searching with PHP and MySQL

Basically, I got the same problem as this guy but I also need relevancy: http://stackoverflow.com/questions/1878899/php-smart-search-engine-to-search-mysql-tables-advice

But, I can't use Sphinx or Lucene (shared hosting)...

I can use LIKE but that has not relevancy so does anyone know a good PHP class that does create relevancy?

So I need something that fits this bill:

Result relevancy value
Match part of words, i.e. "LIKE %searchterm%"
Search multiple database columns
Can't use things like Sphinx or Lucene, no supported by my shared hosting...

I'm using the CakePHP framework.

+1 A:

Zend_Search_Lucene is an implementation of Lucene in pure PHP. I use it on a shared hosting website I run.

lonesomeday 2010-09-27 17:33:54

+2 A:

You might be interested in my presentation Practical Full Text Search in MySQL.

I compare performance of several solutions:

LIKE predicates
MyISAM FULLTEXT indexes
Sphinx Search
Lucene (or Solr)
Google Custom Search Engine
Inverted indexes

If you have no opportunity to install a dedicated search engine, I'd recommend using FULLTEXT or Google CSE.

Using LIKE predicates is pretty clearly the worst choice.

I have tried Zend_Search_Lucene, the pure-PHP clone of Apache Lucene, but the PHP version is much too slow at indexing. See my answer to Can I predict how large my Zend Framework index will be? (and some quick Q:s)

Zend_Search_Lucene also hasn't maintained compatibility with new versions of the Apache Lucene index file format. See Using Solr and Zends Lucene port together…

Bill Karwin 2010-09-27 17:57:13

Thanks Bill, but I really just need something simple that just gives some sort of relevancy to LIKE %% results, the other solutions out of the question atm unfortunatly

Temega 2010-09-27 18:43:02

FULLTEXT indexes are too complex? *Really?*

Bill Karwin 2010-09-27 18:55:28

No, what I meant by simple is a something in the form of a class instead of a full-blow plugin like Sphinx, Solr... I'm already using mysql's fulltext searh, which has relevancy only when not in boolean mode

Temega 2010-09-27 19:00:15

So why not use fulltext searches then? The typical pattern is `SELECT MATCH... AS relevance FROM ... WHERE MATCH...IN BOOLEAN MODE ORDER BY relevance` so you use boolean to find matching rows, and non-boolean to make a relevance column you can sort by.

Bill Karwin 2010-09-27 20:07:52

Because non boolean returns a different result that in boolean?? That makes the relevance irrelevant ;)

Temega 2010-09-27 23:09:29

Okay, but `LIKE` doesn't return relevance, it only returns true/false. Your solution in MySQL to return relevance is FULLTEXT in non-boolean mode. Sorry I can't offer anything different.

Bill Karwin 2010-09-27 23:14:58

I ended up using mysql's full-text search in boolean mode. I also append the * operators to each search term.

user input: tom jones

resulting query: match(name,description) against('tom* jones* IN BOOLEAN MODE');

Thanks for everyone's help.

Temega 2010-09-29 12:33:53

ansaurus

tags:

views:

answers:

Advice on searching with PHP and MySQL

related questions