can these mysql fulltext queries be optimized/changed to run faster?
I have a mysql database table (CommonWordsTable) that contains multiple records of:
"word-A" - "words related to word-A"
For example:
columnA - columnB
chair - furniture;table;office-chair;dining-chair;wooden chair;metal chair
I am currently using mysql fulltext match-against query to match a sentence against those words :
select columnA,
max(match(columnA, columnB) against ('" + sentence + "')) as relevance
from CommonWordsTable
where match(columnA, columnB) against ('" + sentence + "')
group by columnA
order by relevance desc limit 5;
I am using one more query that takes sentence-matching words from RareWordsTable table (with same structure), and matches those words against CommonWordsTable :
select columnA
from CommonWordsTable
where match(columnA, columnB) against(
(select concat(colA, ' ' , colB)
from RareWordsTable
where match(colA) against ('" + sentence + "') limit 1)
)
limit 3;
The first query takes around 50ms or more, and the second one around 100ms or more.
I am using a joint fulltext index on columnA and columnB, and a fulltext index on colA. I am also loading these indexes in cache.
Is there any way to make these queries run faster ?
I have tried running these on machines with higher CPU/Memory, but that did not make much difference.
I have also tried using Sphinx ; it runs faster, but does not give same quality results.
上一篇: 让狮身人面像过滤搜索结果