我的工作环境是PHP/5.2.11,Apache/2.2.13,MySQL 5.0.51a,WIN平台。我使用fulltext检索数据库内容,展示到页面上。一共有3个数据表article1有约1.8W条内容,cat为INDEX,大约28M;article2有7000条内容,大约13M,article3有1.5W条内容,大约25M。我用下面的语句,匹配所有三个数据表,title,content字段名含有文字Mary Barnard,Patricia Beer,Aphra Behn,Judy Blume 和Elizabeth Bowen的,按时间排序。但是整个过程所需的时间很长,我加了段代码,用microtime()统计,需要3.52567887306秒,如何优化我的语句,提高检索速度?(要求三个数据表的title,content字段名内,查询含有ary Barnard,Patricia Beer,Aphra Behn,Judy Blume 和Elizabeth Bowen文字的所有内容)谢谢。
(SELECT title,content,date,cat FROM article1 WHERE cat='novel' AND MATCH (title,content)
AGAINST ('+Mary +Barnard' IN BOOLEAN MODE))
UNION
(SELECT title,content,date,NULL AS cat FROM article2 WHERE MATCH (title,content)
AGAINST ('+Mary +Barnard' IN BOOLEAN MODE))
UNION
(SELECT title,content,date,NULL AS cat FROM article3 WHERE MATCH (title,content)
AGAINST ('+Mary +Barnard' IN BOOLEAN MODE))
UNION
(SELECT title,content,date,cat FROM article1 WHERE cat='novel' AND MATCH (title,content)
AGAINST ('+Patricia +Beer' IN BOOLEAN MODE))
UNION
(SELECT title,content,date,NULL AS cat FROM article2 WHERE MATCH (title,content)
AGAINST ('+Patricia +Beer' IN BOOLEAN MODE))
UNION
(SELECT title,content,date,NULL AS cat FROM article3 WHERE MATCH (title,content)
AGAINST ('+Patricia +Beer' IN BOOLEAN MODE))
UNION
(SELECT title,content,date,cat FROM article1 WHERE cat='novel' AND MATCH (title,content)
AGAINST ('+Aphra +Behn' IN BOOLEAN MODE))
UNION
(SELECT title,content,date,NULL AS cat FROM article2 WHERE MATCH (title,content)
AGAINST ('+Aphra +Behn' IN BOOLEAN MODE))
UNION
(SELECT title,content,date,NULL AS cat FROM article3 WHERE MATCH (title,content)
AGAINST ('+Aphra +Behn' IN BOOLEAN MODE))
UNION
(SELECT title,content,date,cat FROM article1 WHERE cat='novel' AND MATCH (title,content)
AGAINST ('+Judy +Blume' IN BOOLEAN MODE))
UNION
(SELECT title,content,date,NULL AS cat FROM article2 WHERE MATCH (title,content)
AGAINST ('+Judy +Blume' IN BOOLEAN MODE))
UNION
(SELECT title,content,date,NULL AS cat FROM article3 WHERE MATCH (title,content)
AGAINST ('+Judy +Blume' IN BOOLEAN MODE))
UNION
(SELECT title,content,date,cat FROM article1 WHERE cat='novel' AND MATCH (title,content)
AGAINST ('+Elizabeth +Bowen' IN BOOLEAN MODE))
UNION
(SELECT title,content,date,NULL AS cat FROM article2 WHERE MATCH (title,content)
AGAINST ('+Elizabeth +Bowen' IN BOOLEAN MODE))
UNION
(SELECT title,content,date,NULL AS cat FROM article3 WHERE MATCH (title,content)
AGAINST ('+Elizabeth +Bowen' IN BOOLEAN MODE))
Order By date DESC LIMIT 10 //SELECT里的cat可以删除吗?因为cat是INDEX?
(SELECT title,content,date,cat FROM article1 WHERE cat='novel' AND MATCH (title,content)
AGAINST ('+Mary +Barnard' IN BOOLEAN MODE))
UNION
(SELECT title,content,date,NULL AS cat FROM article2 WHERE MATCH (title,content)
AGAINST ('+Mary +Barnard' IN BOOLEAN MODE))
UNION
(SELECT title,content,date,NULL AS cat FROM article3 WHERE MATCH (title,content)
AGAINST ('+Mary +Barnard' IN BOOLEAN MODE))
UNION
(SELECT title,content,date,cat FROM article1 WHERE cat='novel' AND MATCH (title,content)
AGAINST ('+Patricia +Beer' IN BOOLEAN MODE))
UNION
(SELECT title,content,date,NULL AS cat FROM article2 WHERE MATCH (title,content)
AGAINST ('+Patricia +Beer' IN BOOLEAN MODE))
UNION
(SELECT title,content,date,NULL AS cat FROM article3 WHERE MATCH (title,content)
AGAINST ('+Patricia +Beer' IN BOOLEAN MODE))
UNION
(SELECT title,content,date,cat FROM article1 WHERE cat='novel' AND MATCH (title,content)
AGAINST ('+Aphra +Behn' IN BOOLEAN MODE))
UNION
(SELECT title,content,date,NULL AS cat FROM article2 WHERE MATCH (title,content)
AGAINST ('+Aphra +Behn' IN BOOLEAN MODE))
UNION
(SELECT title,content,date,NULL AS cat FROM article3 WHERE MATCH (title,content)
AGAINST ('+Aphra +Behn' IN BOOLEAN MODE))
UNION
(SELECT title,content,date,cat FROM article1 WHERE cat='novel' AND MATCH (title,content)
AGAINST ('+Judy +Blume' IN BOOLEAN MODE))
UNION
(SELECT title,content,date,NULL AS cat FROM article2 WHERE MATCH (title,content)
AGAINST ('+Judy +Blume' IN BOOLEAN MODE))
UNION
(SELECT title,content,date,NULL AS cat FROM article3 WHERE MATCH (title,content)
AGAINST ('+Judy +Blume' IN BOOLEAN MODE))
UNION
(SELECT title,content,date,cat FROM article1 WHERE cat='novel' AND MATCH (title,content)
AGAINST ('+Elizabeth +Bowen' IN BOOLEAN MODE))
UNION
(SELECT title,content,date,NULL AS cat FROM article2 WHERE MATCH (title,content)
AGAINST ('+Elizabeth +Bowen' IN BOOLEAN MODE))
UNION
(SELECT title,content,date,NULL AS cat FROM article3 WHERE MATCH (title,content)
AGAINST ('+Elizabeth +Bowen' IN BOOLEAN MODE))
Order By date DESC LIMIT 10 //SELECT里的cat可以删除吗?因为cat是INDEX?
解决方案 »
- 用的是mysql数据库,有个表每天都会新增100--200万条数据(重新整理在问过啊)
- 请问大家,让 mysql 连 jdbc 使用事务,可以rollback(),是不是还需要mysql什么特殊设置?
- 在linux系统中我用了一个mysql的可视化工具!!mysql-administrator!请问一下这个工具能向数据库表中插入数据吗 ??
- MY-SQL创建数据库出现异常?
- 在MYSQL提示符下輸入連接命令出錯 @_@ 急
- sos~
- 要学数据库那方面的知识,最好选什么书籍学啊??
- 求最大值所对应的日期的sql语句
- 刚学SQL,请教一个问题!
- 500GB 的数据,我该怎么设计数据库?
- 如何能知道存储过程中捕获的是什么异常?
- 请高手指点下,这个sql有点不好写。
ALTER TABLE fulltext_sample ADD FULLTEXT(copy)
主,WHERE中有CAT=,可以用到索引
没找到相关的文档说明。
我想试试合并关键字,变成3个表的联合搜索,可能会比现在分成15个表联合搜索的速度快?
比如 '+apple +(>turnover <strudel)' 中,再加一个pie,要求 turnover相关性最高,strudel其次,pie最弱。
是不是像这样? '+apple +(>turnover >strudel <pie)'
变成3个表的联合搜索,果然提高不多,还是需要2.61234322178秒