[thelist] Complex Search using PHP/MySQL

[Tim Massey]

This may be a bit off topic but then again it may be right on? If its
off topic I would appreciate direction on where to look.

The problem is this - we are developing an internal recruitment
database. The design has been normalised (within reason) and I'm up to
the part of developing the search logic.

The search form is a monster - it includes the following fields, most
are in the candidates table others are in:

* external tables.
** lookup & category/type tables.

First Name, Surname, Gender.
County, Country.
Home & Work Phone, Email, Mobile.
Job Title*
Availability*
Notice Period**
Employment Type**
Contract Rate**
F/T Salary Band**
Job Title(s)**
Skills**
Industries**

And the crown jewel - Boolean search of CV content (text field in
external table). A candidate can have many CV's

My question is - how would you go about filtering the results, using PHP
or rely more on sql? 

I'm thinking to do an initial search on the fields within the candidates
table, generating an array of possible id's then filtering from the
lookup tables to get a reduced array of id's then performing the Boolean
search on the CV's for only those id's in the array.

How would you go about catering for the inevitable performance hit of
such a technique?

Can anyone offer advice or direction?

Tim.