Search strategy for multiple fields in web app

[nagger]

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 tablesSchema goes something like this\[code\]PROJECTSprojectIDprojectTitleprojectTownprojectCountryID (FK to countries table)agencyID (FK to agencies table)COUNTRIEScountryIDcountryNameAGENCIESagencyIDagencyNameTAGS_PROJECTS (many-many relationship between tags and projects)idprojectIDtagIDTAGStagIDtagName\[/code\]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 projectThe 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 tablesI 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 QUERYI 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 withMULTIPLE QUERIESBecause 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 TABLEI was considering writing a row into another table as a handmade 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 indexedTechnologies to hand - PHP 5.1.6 and MySQL 5.0.22 running on RHEL5Any thoughts, advice or war stories welcomeThanks for your timeIan