Site Search

I've got a client that wants a pretty complicated search engine built into their website. I was wondering if any of you smart people had any reading recommendations? I have some idea of what I would like to do but am looking for a "best practices" type approach. Thanks.

That really really depends on what kind of content you have and how it is structured in the DB. A lot of developers who don't have a lot of database knowledge will fail to index fields properly which makes selection slow.

Basically, if you have a field that you might be selecting against, add it as an index. If it's certain to be unique for each record, make it a unique index. If you have bodies of text that you want searchable, add FULLTEXT indices to those fields and read up on how to do FULLTEXT searches http://dev.mysql.com/doc/refman/5.0/...xt-search.html

Be sure to check your queries with EXPLAIN to see if they're as efficient as they could be. If you're still not sure on how you'd implement the above, post with some more info on what kind of data you need searched.

Well, I would like to have the conventional quote marks for exact phrases, the OR, minus sign for not. However, I'm not sure if there would be more options expected that I'm not aware of. ...what do you mean by "EXPLAIN"?

Looking over the specs they sent, it's actually not terribly complicated. I'm looking for some reading to familiarize myself with search systems.

A simple search engine can be done with just raw MySQL queries for sure. Should be pretty easy, but if you want something with intelligent ranking I'd look at one of the packaged solutions.

If it's a high traffic site you may want to look at http://www.sphinxsearch.com/ which is an open source SQL full text search engine much faster than mysql's built in functionality. However, this is only necessary if the website is getting tens of thousands of searches per day.

Anyway, here's a good tutorial on how to do mysql fulltext search: http://devzone.zend.com/article/1304 - It shows you how to do boolean searches like you want.

If the site is not very popular, then simple queries with LIKE would do the job best. Just write a simple search query parser which would allow users to apply AND, OR and wildcards to it.

MySQL FULLTEXT is for MyISAM tables only and has some other limitations. And it has poor performance. Just an example benchmark. So if you're running a busy site and have a VPS or a Dedicated server, then Sphinx is the best option.

Summary: PHP+LIKE for small sites, Sphinx for big sites and FULLTEXT for intermediate sites with MyISAM tables which have not been filled with much data yet.

Thanks! That was pretty much exactly what I was looking for. And now I have a better idea of what to search for. Awesome.