Search strategy for multiple fields in web app

Got a web app in development; requirement is to provide a single search field which searches important fields in main table plus other fields related by PK/FK from joined tables

Schema goes something like this

PROJECTS
projectID
projectTitle
projectTown
projectCountryID (FK to countries table)
agencyID (FK to agencies table)

COUNTRIES
countryID
countryName

AGENCIES
agencyID
agencyName

TAGS_PROJECTS (many-many relationship between tags and projects)
id
projectID
tagID

TAGS
tagID
tagName

So the user would enter a search term and we want to see if it occurs within projects.projectTitle, projects.projectTown, countries.countryName, agencies.agencyName, or tags.tagName for any tags assigned to a project

The dataset will grow over time to be on the order of 10,000-50,000 rows in the projects table, and 000's in the other tables

I am going to set up a test rig and run tests of different approaches, but I wondered if anyone had dealt with a similar situation before and had any advice to offer?

The possible approaches I am considering and will test are:

SINGLE QUERY I imagine it will be possible to write a single SQL query to do the search, but such a query would probably perform poorly without careful optimisation once the data has grown to its full size. Trouble is I won't be involved after project launch, and so won't have full, real data to experiment with

MULTIPLE QUERIES Because the site and DB will be lightly loaded, a few small queries will probably be at least as quick and simpler to code for. Would issue several SQL queries and then merge the resultsets in PHP for each search.

REDUNDANT SEARCH TABLE I was considering writing a row into another table as a handma开发者_Python百科de index whenever the project is edited - this would take the text values from the related fields for tags, country, agency, etc, concatenate them into a string and stick it into a search table with the projectID; there would be one row in the project table for each project, essentially representing a denormalised view of the key data that we can search.

I've looked into using MySQL views a little, but am nervous of the lack of indexing on these; at least the redundant search table can be carefully indexed

Technologies to hand - PHP 5.1.6 and MySQL 5.0.22 running on RHEL5

Any thoughts, advice or war stories welcome

Thanks for your time

Ian

---

I would definitely look into the FULLTEXT functionality of MySQL for this. I have already answered a question regarding different search approaches, and that solution is mostly what you would require.

Basically, in the FULLTEXT example described in that answer, you can simply create your temporary table using a SELECT which would declare all the fields of all the tables you wish to search from (+ their index column) and declare your FULLTEXT index on those fields. Then simply query that temporary table...

---

I would suggest that instead of building this search functionality all by yourself, you could use a specific search product of library. You could for instance use SOLR. With such a product it's usually fairly easy to let it index a datasource, in this case a database. In the case of SOLR you could

Using SOLR for instance would give you a lot of extra and advanced features, you could use easily in your web-app. For instance faceted search, spelling suggestions, finding synonyms, functionality to find similar words(for typos) not only exact matches and many more: features of SOLR

There's also SOLR client code available for PHP: http://code.google.com/p/solr-php-client/

Besides SOLR there are many other search products to achieve this kind of search functionality, both free and commercial.

It's not impossible to build this kind of functionality yourself, but using a mature product you get a tested product and a lot of features that improve the user experience that are very hard to build.