Bug 2512

Summary: SQL: Improve (i)like preformance on diverse stringdata
Product: SQL Reporter: Stefan de Konink <skinkie>
Component: allAssignee: SQL devs <bugs-sql>
Status: NEW ---    
Severity: enhancement    
Priority: ---    
Version: -- development   
Hardware: x86 (i?86)   
OS: Linux   
URL: http://sourceforge.net/support/tracker.php?aid=2902566

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, bugs-sql@monetdb.org.
   Previous assignee was nobody@users.sourceforge.net.