I am making a mysql fulltext search.
my database table article1
has ~18000
articles, article2
has ~7000
articles, article3
has ~13000
articles. FIELD cat is a INDEX field
Now I want to make a union search. there are 5 groups words put into 3 table, match out the results. But the process time is 3.1213495136 seconds
. (I add microtime()
to see how much time it will cost). Is there any way to optimize mysql fulltext union search? Thanks.
(SELECT title,content,date FROM article1 WHERE
(cat='novel' AND MATCH (title,content) AGAINST ('+Mary +Barnard' IN BOOLEAN MODE))
OR
(cat='novel' AND MATCH (title,content) AGAINST ('+Patricia +Beer' IN BOOLEAN MODE))
OR
(cat='novel' AND MATCH (title,content) AGAINST ('+Aphra +Behn' IN BOOLEAN MODE))
OR
(cat='novel' AND MATCH (title,content) AGAINST ('+Judy +Blume' IN BOOLEAN MODE))
OR
(cat='novel' AND MATCH (title,content) AGAINST ('+Elizabeth +Bowen' IN BOOLEAN MODE)))
UNION
(SELECT title,content,date FROM article2 WHERE
(MATCH (title,content) AGAINST ('+Mary +Barnard' IN BOOLEAN MODE))
OR
(MATCH (title,content) AGAINST ('+Patricia +Beer' IN BOOLEAN MODE))
OR
(MATCH (title,content) AGAINST ('+Aphra +Behn' IN BOOLEAN MODE))
OR
(MATCH (title,content) AGAINST ('+Judy +Blume' IN BOOLEAN MODE))
OR
(MATCH (title,content)AGAINST ('+Elizabeth +Bowen' IN BOOLEAN MODE)))
UNION
(SELECT title,content,date FROM article3 WHERE
(MATCH (title,content) AGAINST ('+Mary +Barnard' IN BOOLEAN MODE))
OR
(MATCH (title,content) AGAINST ('+Patricia +Beer' IN BOOLEAN MODE))
OR
(MATCH (title,content) AGAINST ('+Aphra +Behn' IN BOOLEAN MODE))
OR
(MATCH (title,content) AGAINST ('+Judy +Blume' IN BOOLEAN MODE))
OR
(MATCH (title,content)AGAINST ('+Elizabeth +Bowen' IN BOOLEAN MODE)))
Order By date DESC LIMIT 10