Improving Full Text Search MYSQL

Question

I have a search engine based site that is currently in beta mode http://www.jobportfolio.co.uk. The site has a job table that incorporates the following fields, (job_company, job_title, job_description, job_location) all the fields are Var except for description that is a text field. All the fields are indexed as FullText.

My current approach is to search based on the title, location and company. This seems to work fine however I would like to improve the search results by adding in the description field. The problem is however when I add the description field the search seems to take a lot longer. Even with a table that only contains 12000 rows it seems to be slow.

I am using the following MATCH AGAINST query to select the results

MATCH(job_posts.job_title, job_company) AGAINST('".$this->mysqli_escape($job_title)."' IN BOOLEAN MODE)

Does anyone have any opinions on how to improve the performance of the search?

do you have one single fulltext index that covers those fields, or multiple fulltext indexes that cover some/part of the fields? — Marc B
I use PHPMYADMIN and have an fulltext index for each of the fields seperately. Is it better to have one index to cover them all? — ORStudios

Topher Hunt Topher Hunt · Accepted Answer · 2012-10-09T17:57:59

Hm, my first thought is to approach this problem from the "outside": is it acceptable to have a search form that uses multiple different fields? If you're willing to have 4 search strings that each search in a different column, I suspect that will reduce load by itself. For example:

When someone types in the "location" field, you add a clause to the query that matches the searched text against the location field only.

When someone types in the "description" field, you add a clause to the query that matches the search text against the description field. Otherwise you don't match anything against the description field.

If you don't need to be able to enter text into one place and search "all possible fields" for it, this solution will prevent extra slowness until someone specifically wants to search in the description text. So the query speed varies based on the searcher's needs.

Improving Full Text Search MYSQL

1 Answers