>Now, I wonder if something similar could actually be done internally by >Firebird for the general like case? >It could some nifty query logic like the above internally, could it not? If >"like xxx" always generated >internally a query logic like this: > >where myfield starting with <prefixof :param up to but not including first >wildcard> > and myfield like :param
>I assume starting with will return true for all data values if the prefix is >an empty string - is this true? >In that case, would this internal query return the same results as the >direct/simple "myfield like :param" >query? And would it actually give better performance in general? Would it risk >significantly worse >performance in any situation? I tried manually LIKE with and without the addition of STARTING WITH and tried what I thought was likely to have worse performance with STARTING WITH - when the parameter starts with a wildcard. With STARTING WITH, my query executed in 5.4 seconds, without it 4.6 seconds. So, in my simple test, the additional overhead was about 17%. The other extreme, with the wildcard at the end, STARTING WITH executed in 0.3 seconds, vs 4.5 seconds without STARTING WITH, i.e. STARTING WITH is 15 times quicker. So, what determines whether such an addition will be harmful or benefitial, is (no surprise) what the parameter looks like. To me, a question regarding such an addition is whether one should add a 17% overhead to people being careful about their programming to delight those that are more sloppy in their programming with huge speed increases? I'd say no, anyone is free to add STARTING WITH to their own queries and I don't like the idea of forcing it upon those that doesn't need it. Mind you, it could be a sensible addition to component sets and I wouldn't mind if e.g. IBO added a Boolean property LikeAddsStartingWith to TIB_Cursors, that - if TRUE - did what you want Firebird itself to do. Myself, I rarely use LIKE in my queries, particularly not as the only limiting factor on huge tables (if you have other, noticeably more selective indexes available for the query, there's no point in adding STARTING WITH), and am not amongst those that are likely to benefit or be harmed from your proposed addition to Firebird. Set
