i've a big problem using a fullText search in my innoDB database.
First of all the ns_pages table have more then 2.6m of records, with fulltext index with 3 keyblock.
This DB run on a Dell R710 with 128GB of Ram.
When i use this Query, the DB stop work.
if i check the query using webmin i see that the query still work until timeout.
The query is pretty Easy but i don't know why it's don't work.
SELECT sh.id,body, `source`, `page_in_source`, `datepaper`, `folder`
, `attach_fn`, Description,s.ImgFName
FROM ns_pages sh INNER JOIN ns_source s on s.id=sh.source where viewnews=1
AND DATE_ADD(sh.datepaper,INTERVAL s.days_in_advance DAY)
BETWEEN '2017-12-22' AND '2017-12-22'
AND source in (1815,345,1,382,89,14,12,239,108,119,485,490,13,509,2,537,182,193,333,559,1817,111,506,1615,1752,248,33,34,35,357,36,458,131,26,27,32,30,29,31,28,1816)
AND MATCH (body) AGAINST ('"United States of America"' IN BOOLEAN MODE)
order by datepaper, DistributionArea, cadence, Description, page_in_source desc
but the simple one work well
SELECT sh.id,body, `source`, `page_in_source`, `datepaper`, `folder`
, `attach_fn`, Description,s.ImgFName
FROM ns_pages sh INNER JOIN ns_source s on s.id=sh.source where viewnews=1
AND DATE_ADD(sh.datepaper,INTERVAL s.days_in_advance DAY)
BETWEEN '2017-12-22' AND '2017-12-22'
AND source in (1815,345,1,382,89,14,12,239,108,119,485,490,13,509,2,537,182,193,333,559,1817,111,506,1615,1752,248,33,34,35,357,36,458,131,26,27,32,30,29,31,28,1816)
AND MATCH (body) AGAINST ('Jamaica' IN BOOLEAN MODE)
order by datepaper, DistributionArea, cadence, Description, page_in_source desc
