Bug 2512 - SQL: Improve (i)like preformance on diverse stringdata
Summary: SQL: Improve (i)like preformance on diverse stringdata
Status: NEW
Alias: None
Product: SQL
Classification: Unclassified
Component: all (show other bugs)
Version: -- development
Hardware: x86 (i?86) Linux
: --- enhancement
Assignee: SQL devs
URL: http://sourceforge.net/support/tracke...
Depends on:
Reported: 2009-11-23 05:12 CET by Stefan de Konink
Modified: 2016-04-11 11:45 CEST (History)
0 users


Note You need to log in before you can comment on or make changes to this bug.
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.