like中文查询大数据量效率太差的问题

Question

信息如下：
mysql版本为5.1.60
表：post
字段         类型
id           AUTO_INCREMENT(主键)
userid       int
content      varchar（索引）
post_data    timestamp
is_dup       int使用分页查询的方式去获取数据，查询的是关键字，
比如content="明天从上海出发去重庆"，使用“上海”关键字去搜索
关键字可以是多个，并且有两种规则：and、or，举例“上海 or 重庆”、“上海 and 重庆”
分页查询分两个步骤
1.获取符合查询的总个数、最大id、最小id
  语句：select count(*) ，max(id),min(id) from post where content like '%上海%'
2.在根据id再去获取分页数据
  语句：select * from post where id>$id and content like '%上海%' limit 30当数据总量为1w时，查询需要1秒；但当数据总量为50w时，查询需要30秒
尝试的解决方案
1.使用sphinx+coreseek来查询，但测试的结果发现coreseek查询不准确(使用like查询得到的结果是18w多条，而使用coreseek得到的只有16w多条)
2.使用mysqlcft中文插件，结果也是查询不准确请问怎样才能查询准确度达到like并且效率很高,使用新版本的mysql有帮助吗

Answer 1

用like顺序扫描效率高不了  肯定更用专门的搜索程序

Answer 2

to 版主：
5.1.60 版本全文索引不支援中文搜索
coreseek及mysqlcft搜索精确度达不到要求

Answer 3

有一个土办法，可以试一试
1、建立一个关键字表CREATE TABLE `t_key` (
  `fkey` varchar(45) NOT NULL,
  PRIMARY KEY (`fname`)
) ENGINE=InnoDB DEFAULT CHARSET=utf82、建立一个查询缓存表CREATE TABLE `t_search_cache` (
  `fid` int(11) NOT NULL AUTO_INCREMENT,
  `fcontent` varchar(45) DEFAULT NULL,
  `freference_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`fid`),
  KEY `xxxx_idx` (`freference_id`),
  KEY `content_idx` (`fcontent`),
  CONSTRAINT `t_cache_fk` FOREIGN KEY (`freference_id`) REFERENCES `t_post` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf83、建立2个triggerdelimiter $$
create trigger t_cache_add_trigger after insert on t_table1 for each row
begin
insert into
t_search_cache
(fcontent,freference_id)
select
t_key.fkey,
NEW.fid
from
t_key
where
POSITION(t_key.fname IN NEW.fcol2)!=0;
end$$
[code]
[code=sql]
delimiter $$
create trigger t_cache_remove_trigger before delete on post for each row
begin
delete
from
t_search_cache
where
t_search_cache.freference_id=OLD.id;
end$$每次查询执行以下语句select
post.*
from
post
inner join
t_search_cache
on
t_search_cache.freference_id=post.id
where
t_search_cache.fcontent = "somekeywork"
当然，以上方法必须事先维护好关键字表t_key，如果新加关键字只对新纪录生效，老记录就没办法了。不知对你的问题有帮助否。

Answer 4

上述有误，应将t_table1改成post

Answer 5

LIKE '%X%' 这类查询只能通过全文索引来优化。 目前针对中文的几种全文索引补丁均有缺陷。

Answer 6

待会试一下赵兄的方案
目前在看xunsearch中文搜索引擎，不知道大家用过没

Answer 7

在该字段上创建前缀索引会好些，同时如果想like 走索引，请用 like '搜索关键字%'形式

Answer 8

因为关键字的位置不固定，也不清楚用户使用何种关键字，所以在索引及like上改善应该没啥效果

Answer 9

说一下使用xunsearch全文搜索引擎的感受
1.速度没有sphinx快，查询时加上排序则更慢
2.排序功能待改善：id=117 排在id=12前面,时间排序只精确到分钟
3.准确度高于sphinx（同like语句比较）