MySQL Forums
Forum List  »  Newbie

fulltext vs direct search
Posted by: Leonardo Salvini
Date: August 16, 2004 02:22AM

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?

Options: ReplyQuote


Subject
Written By
Posted
fulltext vs direct search
August 16, 2004 02:22AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.