Approaches to making custom site search

I'm making a social website with lots of different sections, like blogs, galleries, multimedia etc. And now the time has come to implement the search functionality. Customer refused to use google search and insisted on making custom one, where results will be shown for each section individually.

For example, if user enters 'art', the result should be displayed like this:

3 found in blogs
1 ...
2 ...
3 ...

2 found in galleries
1 ...
2 ...

None found in multimedia

I'm planning to use MySQL fulltext search for this. So, the question is: How do I make such search, so it开发者_开发知识库 won't kill the server if very many records match the query? I don't really see how to implement paging in this case.

I would highly recommend NOT using MySQL for full text search, it is slow both in index creation and in performing searches.

Take a look at Sphinx or Lucene, both of which are significantly faster than MySQL and which bind quite readily to PHP applications.

You wont kill a mysql server with such a thing, even if your app is huge (we are talking about thousands of queries/sec here) you will just have to set up a replicate of your mysql server dedicaced to search, you may want to build a cache of "popular keyword results" for speeding things up a bit, but appliances likes a googlemini is still the best for that ...

If you can run a Java servlet container (like Tomcat or Jetty), then I recommend Solr (http://lucene.apache.org/solr/). It sits on top of Lucene and is very powerful. Solr was started at CNET and is used by big sites like Netflix and Zappos. Stack Overflow uses a .NET implementation of Lucene. I'm not familiar with Sphinx, so I can't tell you how it compares to Solr.

If you use Solr, look into faceting. This allows you to perform a search and then get a count of how many documents were in "blogs", "galleries", "multimedia', etc.

Here is a PHP client to interface with Solr (http://code.google.com/p/solr-php-client/).

Maybe better decision is use - sphinx

I've done this before on some sites I created. What I have done is run one query against each module to find the results. What you want to do is run a mysql query, and then fetch rows in a while loop rather than using a fetch all. This will make sure you don't over consume memory.

for example: while($row = mysql_fetch_array($result)){ echo $row['item_name']; }

You will most likely find that MySQL can handle much larger searches than you think.

Pagination is best done with a paging class, like one from code igniter or the like. Are you using a web frame work?

Yes Sphinx or Lucene, both are good and significantly faster than MySQL and which bind quite readily to PHP applications.