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