问题描述
这是医疗MIS表的数据库结构:
Here is the database structure for a medical MIS table:
CREATE TABLE IF NOT EXISTS `ab_med` (
`med_id` bigint(20) NOT NULL AUTO_INCREMENT,
`med_title` varchar(200) DEFAULT NULL,
`med_posted_date` varchar(200) DEFAULT NULL,
`med_company_name` varchar(200) DEFAULT NULL,
`med_experience` varchar(200) DEFAULT NULL,
`med_salary` varchar(200) DEFAULT NULL,
`med_city` varchar(200) DEFAULT NULL,
`med_description_short` text,
`med_details_link` varchar(500) DEFAULT NULL,
`med_from_city_type` int(1) NOT NULL DEFAULT '1',
`med_from_city` varchar(255) DEFAULT NULL,
`med_collected_time` datetime DEFAULT NULL,
`med_status` int(1) NOT NULL,
PRIMARY KEY (`med_id`),
KEY `MedCity` (`med_city`),
KEY `MedTitle` (`med_title`),
KEY `MedCompany` (`med_company_name`),
KEY `MedExperience` (`med_experience`),
KEY `med_details_link` (`med_details_link`),
FULLTEXT KEY `med_index` (`med_title`,`med_company_name`,`med_description_short`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=639295 ;
大约有50万条记录.用户可以使用2个字段进行搜索:
There are around 0.5 million records. A user can search using 2 fields:
- 关键字
- 位置
根据得分将关键字排入med_title
,med_company_name
,med_description_short
,med_city
.
Keyword is looked into med_title
, med_company_name
, med_description_short
, med_city
as per the score.
位置已查看到med_city
我使用了全文mysql搜索,但是我需要最新的条目才能显示出更好的分数.我没有得到它的SQL查询.我正在使用以下SQL查询:
I've used full text mysql search but I need the most recent entries to show up with better score. I'm not getting the SQL query for it. I'm using the following SQL query:
SELECT distinct(med_details_link),med_id,med_title,med_posted_date,med_company_name,med_description_short,med_from_city,med_experience,med_city,med_collected_time,MATCH (med_title,med_company_name,med_description_short) AGAINST ('+hello +world' IN BOOLEAN MODE) as score
FROM ab_med
WHERE MATCH (med_title,med_company_name,med_description_short) AGAINST ('+hello +world' IN BOOLEAN MODE) order by med_id desc limit 1000
假设我搜索"hello world",我了解应该基于以下因素来权重:
Suppose I search for "hello world", I understand that there should be weight based on:
- 标题的权重最高
- 公司名称的权重小于标题,但权重大于描述
- 说明的权重最小
请帮助获取SQL查询以考虑所有这些权重并有效地进行搜索.
Please help in getting a SQL query to consider all these weights and search efficiently.
推荐答案
请考虑使用Sphinx获得令人满意的解决方案.
Consider turning to Sphinx to get a satisfactory solution to this problem.
有500万行,并且有您的要求,MySQL全文搜索的性能将令人失望.
With 5 million rows, and the requirements you have, the performance of MySQL fulltext search will be disappointing.
这篇关于MySQL查询-基于权重的搜索引擎的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!