|Summary:||SQL: Improve (i)like preformance on diverse stringdata|
|Product:||SQL||Reporter:||Stefan de Konink <skinkie>|
|Component:||all||Assignee:||SQL devs <bugs-sql>|
Description Stefan de Konink 2009-11-23 17:12:56 CET
Using an 'index' that is able to differentiate on stringlength a typical string lookup can be improved. Using a 2.4M row string table, the (smallint) lookup consisting of string length is done in less than 200ms. The same 'fulltext' operation using %the string%, takes around 2500ms. Using a combination string_size >= length('string') and string ilike '%string%' the first lookup time can be reduced. The (i)like operation has be striped from modifiers, due to the constant length we can assume that the string therefore always be longer.
Comment 1 Pseudo user for Sourceforge import 2010-05-04 09:32:09 CEST
This bug was previously known as tracker item 2902566 at http://sourceforge.net/support/tracker.php?aid=2902566 The original assignee of this bug does not have an account here. Reassigning to the default assignee for the component, firstname.lastname@example.org. Previous assignee was email@example.com.