改进全文搜索MYSQL [英] Improving Full Text Search MYSQL

问题描述

我有一个基于搜索引擎的网站，目前处于测试模式。 http://www.jobportfolio.co.uk 。该网站有一个包含以下字段的作业表（job_company，job_title，job_description，job_location），除了描述是文本字段外，所有字段均为Var。所有字段都被索引为FullText。

我目前的方法是根据标题，位置和公司进行搜索。这似乎工作正常，但我想通过添加描述字段来改善搜索结果。但是，当我添加说明字段时，搜索似乎要花费更长时间。即使对于只包含12000行的表，它似乎也很慢。

我使用以下MATCH AGAINST查询来选择结果

  MATCH（job_posts.job_title，job_company）AGAINST（'。$ this-> mysqli_escape（$ job_title）。''布尔模式）
  

有没有人对如何改善搜索效果有任何意见？

当某人在位置字段中键入内容时，您只会在查询中添加一个与搜索到的文本仅与位置字段相匹配的子句。

当某人在description字段中键入内容时，您会在查询中添加一个与搜索文本与description字段相匹配的子句。如果您不需要将文本输入到一个地方并搜索所有可能的字段为它，这种解决方案将防止额外的缓慢，直到有人特意要在描述文本中搜索。所以查询速度根据搜索者的需要而变化。

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?

解决方案

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.

这篇关于改进全文搜索MYSQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！