Job Search Database

Database programming and setup - anyone did a job search web site?

I have questions about creating a job search web site. When a user type in a word on the "keyword" field on the form then what field on the database table it should use? The job records have job description and job title field but that description field has lots of words in it.

Even that field is indexed, the sql query will search for something like %sales% in MySQL. Will that be a big issue in terms of performance and speed? Should I have an extra field on the job table contains specific skills field so it will be smaller to search for instead of using description?

Any idea will be welcome.

Thanks.

I can't speak for MySQL but if it is similar to Oracle, make sure that you are using a parameterized query for your query. If you don't, the database will need to create a new plan to search the data for every query rather than reusing an existing plan. Another option for the keyword searching is to run a period "keyword" optimization process periodically based on popular keyword searches. If you many people are searching for "entry level" as a key word, you could create an index to lookup all entries that contain the key word during an off peak time. The query interface could first check against the index and if it finds a keyword you haven't used before then it would run a straight query against the data. Just keep stats on what the common query key words are and you could optimize a bit.

If you decide to allow posters of the job to enter key words, you could find that they are a bit inaccurate. Some people could enter key words that aren't in the job description just to ensure it is found. If you do choose to use this, be sure to check that the keyword is somewhere in the job posting text before allowing the record commit.