fulltext vs direct search
Hi,
I do the following search on a 10 million rows table:
SELECT mid(msg,locate('/',msg,INSTR(msg,'('))+1,locate(')',msg,INSTR(msg,'for'))-(locate('/',msg,INSTR(msg,'('))+1)) as port,
count(*) as N
FROM `logs`
where date between 'start_date' and 'end_date' and
host='xxx.xxx.xxx.xxx' and
INSTR(msg,'Built Outbound')>0
group by port
order by 2 desc
limit 10
I have 2 indexes set on 'host' and 'date' fields.
'msg' is a Text field with no Index set.
for short intervals (2 days) between 'start_date' and 'end_date' I have good results (<60 sec)
For greater intervals (>=3 days) it takes too long (>300 sec).
So I tryed to use fulltext search as I read that it could improve the speed of my query.
I added a fulltext index on the msg field and the query was changed this way:
SELECT mid(msg,locate('/',msg,INSTR(msg,'('))+1,locate(')',msg,INSTR(msg,'for'))-(locate('/',msg,INSTR(msg,'('))+1)) as port,
count(*) as N
FROM `logs`
where date between 'start_date' and 'end_date' and
host='xxx.xxx.xxx.xxx' and
MATCH (msg) AGAINST ('"Built Outbound"' IN BOOLEAN MODE)
group by port
order by 2 desc
limit 10
But I had a bad surprise:
Even with the shortest date interval (1 day) it takes a very long time (> 800 sec).
so the question is why the direct text search is faster than the fulltext one?