0004776: Optimization suggestions for SUBSTR and LOCATE

Viewing Issue Simple Details [ Jump to Notes ]

[ View Advanced ] [ Issue History ] [ Print ]

Category

Severity

Reproducibility

Date Submitted

Last Update

[VCOMPONENT-VKERNEL] 0004776

Performance

tweak

always

2010-03-04 21:06

Reporter

Kem Tekinay

View Status

public

Assigned To

Priority

normal

Resolution

open

Status

new

Product Version

4.5.1

Summary

0004776: Optimization suggestions for SUBSTR and LOCATE

Description

LOCATE( str, fld ) = num should optimize to SUBSTR( fld, num, LENGTH( str ) ) = str. SUBSTR( fld, 1, cnt ) = str should optimize to LEFT( fld, cnt ) = str. In my database, when I use WHERE LOCATE( str, fld ) = num, it takes about 17 seconds, but WHERE SUBSTR( fld, num, cnt ) = str takes 12 seconds, an improvement. Going one step further, if Valentina looks for cases where num = 1 in SUBSTR( fld, num, cnt ), it should use WHERE LEFT( fld, cnt ) = str instead. That takes 0.006 seconds. So, if I write my query as WHERE LOCATE( str, fld ) = 1, it should ultimately optimize to LEFT( fld, Length( str ) ) = str.

Additional Information

Issue History
Date Modified	Username	Field	Change
2010-03-04 21:06	Kem Tekinay	New Issue

Relationships

Notes
There are no notes attached to this issue.