On 2008-04-19 at 17:18:43 [+0200], Martin Edenhofer
On Apr 19, 2008, at 17:06 , Oliver Tappe wrote:
[ ... ]
Yes, on a), b) and c) I only created lower case indexes and lower case queries, so SQL LOWER was not needed anymore. :)
Getting rid of LOWER is good. It would be great if we'd be able to get rid of LIKE, too - especially the version starting with a %. But I am a bit puzzled: what index did you create for strategy a)?
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?
Well, we could pick up the 8000 matching article IDs from the DB and then prepare the second SQL (the one that does the permission-related and other checks) with a large IN clause for the IDs (containing maybe 500 or 1000 ?-placeholders). That statement would then be executed in a loop (passing in the next 500 or 1000 IDs via bind params in each round). But all this would have to be tested and tried for every DBMS, I'm afraid, as some are probably going to have limits on the number of bind params that can bee used in a single statement. I think if we do some testing across DBMSes, we will find out that some are faster with one big statement (doing keyword search and everything else in one go) and others are faster with the split approach. 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!