On 2008-04-19 at 16:15:55 [+0200], Martin Edenhofer
On Apr 19, 2008, at 15:51 , 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*).
Of course, they also would support other cool features like "sounds like"... :)
Yep, and they provide a relevance ranking, too :-) [ ... ]
- 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.
So the problem is, that I only need to search article where I have ro access.
- In strategy b), there was an index on search_words.word, right? ;-)
Yes. :)
- The two datasets you mentioned - did you create them artificially or are they copies of real setups (i.e. is the content "real" or computer generated)?
Yes. Data from live databases.
Ah, good. cheers, Oliver -- ((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!