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
