Re: [dev] Ideas about Fulltext Searching (performance)?

[Martin Edenhofer]

Hi,

On Apr 19, 2008, at 17:06 , Oliver Tappe wrote:
One idea would be to make use of any fulltext indexing capability
that the
DBMS provides. I have no idea if all supported DBMS provide such a
feature
but I do know that mysql, oracle and mssql have it.

I tried this for MySQL and DB2. The problem on both is they only
create indexes on words (so far as I figured out). So I was running
into not finding ticket#... so I tried other ideas... but maybe I'm
wrong.

I think with mysql searching for "ticket#1234" should work and you  
can even
apply globs, too (conten*).

Ok, I'll have a second look on it...

- What were the exact SQL-statements you used to yield those  
numbers?

May be this is the main problem. A ticket search means I need to
search over different tables and attributes. So in this case I need  
to
involve the ticket, article, queue table. I also need to keep the
permissions on ticket/queues in mind.

A query looks like:

SELECT DISTINCT st.id, st.tn, st.create_time_unix FROM ticket st,
queue sq , article at WHERE sq.id = st.queue_id AND st.id =
at.ticket_id AND sq.group_id IN (1, 2, 240, 3, 31, 312, 32)  AND
( LOWER(at.a_body) LIKE LOWER('%smith%')) ORDER BY  
st.create_time_unix
DESC

Yes, I know how the standard OTRS search query looks like, but for  
strategy
b) you'd have to use a different statement, as otherwise the index  
would
never be used. Did the SQL statement for strategy b) still include
"LOWER(at.a_body) LIKE LOWER('%smith%')"?

In general, I'd suggest to evaluate searching through articles in an  
isolated
manner (leaving out all the other search keys and the access  
rights). This
way it is much easier to compare the performance of the different  
searching
strategies (as there won't be any side-effects by all those other  
search
parameters).
It would always be possible to do any fulltext search first and then  
reduce
the results of that by applying all the other checks in a second SQL
statement. If no keywords have been entered, the first (fulltext)  
part could
be skipped, of course.


Yes, on a), b) and c) I only created lower case indexes and lower case  
queries, so SQL LOWER was not needed anymore. :)

I also like the idea to split/isolate fulltext searches form  
permission and other ticket attributes.

But I do see one problem. Maybe you have an Idea.

In case I'm searching for, lets say "bmw" and I get 8.000 hits. So I  
take this 8.000 out of the database, then I need to do the permission  
check (and in case, maybe other ticket attribute checks like state,  
created, ...). But just focused on permission check will kill my  
winning time of fulltext search (because of DB lookups for permission  
check). Do you have an idea how to handle this?

 -Martin

--
((otrs)) :: OTRS AG :: Europaring 4 :: D - 94315 Straubing
  Fon: +49 (0) 9421 56818 0 :: Fax: +49 (0) 9421 56818 18
    http://www.otrs.com/ :: Communication with success!

Address of record: Bad Homburg
Local Court: Bad Homburg, HRB 10751
Tax number: 003/240/97505

Chairman: Burchard Steinbild
Chief Executive Officer: André Mindermann

_______________________________________________
OTRS mailing list: dev - Webpage: http://otrs.org/
Archive: http://lists.otrs.org/pipermail/dev
To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/dev