User content site wide search - PHP/ MySQL

Question

For a user content website I am creating, it has lots of sub-sections: Movies, Jobs, People, Photos, Mail, etc. It's like a yahoo portal but very very detailed with information search, like I a niching as deep as possible per topic unlike any site out there. I have the site being developed in codeignitor php and mysql. Search can be global across all sub-sites and per sub-section as we see on google, yahoo. There are 22 possible user content objects on my system, each has about 12-15 search fields which i call object meta data + I a storing historical data (like user content version control) also which i want to include in the search.

Now the question is for per sub-section search it seems reasonable because the scope is limited so I think I can pull it off well with mysql. I don't foresee any performance issue. But for site wide search it will search not just title names, but keywords, tags, description, including people's mail, comments, historical data etc. So my worry is performance. Since this is a startup, I have limited hardware resources, so I have to depend 100% on the database and code to pull it off.

So what are the best practices for implementing such a search from the code and database point of view and should a mixture of databases be used depending on the sub-site? Currently everything is stored in 1 mysql database. But I see issues where it may work fine for people search, movies search, etc but not if i include mail search, geo locations, historical data search and even having to go searching items like photo tags, photo descriptions, etc -> all part of the global search there can be performance issues due to the high number of joins and number of rows.

Answer 1

I don't know about PHP, but for my ruby-on-rails projects, I always use Sphinx search engine to do such things. It is a standalone search engine that indexes your database, and when the user submits a search query, the query is matched against Sphinx's index database instead of the actual db. It is blazingly fast, and offers great control over how to index/search.
Sphinx Search Engine
PHP: Sphinx Extension (not sure if this is relevant)

Answer 2

For a generalized site wide search on a budget you could constrain one of the major search APIs to just your domain and handle and display results as if they had come from your own search.

Answer 3

I don't have a solution exactly, but am running into a similar problem with my in-development website.

I'm beginning to think a solution might lie in determining where the bulk of your searches lie, and limiting searches to those queries. If the user search requires a bit more in-depth results (such as your mail search, geo locations, historical data) then you can send the user to a second mysql query. Get the majority of your users to search using your simpler, low-performance queries, and the rest can use more resources if necessary.

As an example, majority of my site's users will be searching the news, calendar, and media sections, so my search looks their first. But visitors could also be searching for other users, groups, forum posts, tags/categories, and so on. But I'm going to let a second, more complicated script handle that.