[MGNLUI-7755] Ability to improve JCR filtering performance by using STARTSWITH instead of CONTAINS operator Created: 19/Jan/23  Updated: 14/Feb/23  Resolved: 14/Feb/23

Status: Closed
Project: Magnolia UI
Component/s: None
Affects Version/s: None
Fix Version/s: 6.3, 6.2.29

Type: Improvement Priority: Neutral
Reporter: Rico Jansen Assignee: Sang Ngo Huu
Resolution: Fixed Votes: 0
Labels: None
Σ Remaining Estimate: Not Specified Remaining Estimate: Not Specified
Σ Time Spent: 2d 2.5h Time Spent: 6.5h
Σ Original Estimate: Not Specified Original Estimate: Not Specified

Issue Links:
Issue split
split from MAGNOLIA-8347 JcrQueryBuilder performance issues. Closed
Problem/Incident
causality
Sub-Tasks:
Key
Summary
Type
Status
Assignee
MGNLUI-7789 Implementation Sub-task Completed Sang Ngo Huu  
MGNLUI-7790 Review Sub-task Completed Roman Kovařík  
MGNLUI-7791 Pre-int QA + PM Sub-task Completed Quach Hao Thien  
MGNLUI-7792 QA Sub-task Closed Antonín Juran  
MGNLUI-7821 Documentation Sub-task Completed Roman Kovařík  
Template:
Acceptance criteria:
Empty
Task DoD:
[X]* Doc/release notes changes? Comment present?
[X]* Downstream builds green?
[X]* Solution information and context easily available?
[X]* Tests
[X]* FixVersion filled and not yet released
[ ]  Architecture Decision Record (ADR)
Documentation update required:
Yes
Date of First Response:
Epic Link: [JCR] Search Performance issues
Sprint: Nucleus 29
Story Points: 3
Team: Nucleus
Work Started:

 Description   

Steps to reproduce

  1. Go to pages app
  2. Type "test" into filter of "Title" column
  3. Inspect query statement in JcrQueryBuilder#build() method (q.getStatement())

Actual result:

SELECT * FROM [nt:base] AS t WHERE ISDESCENDANTNODE(t, [/]) AND t.[jcr:primaryType] = 'mgnl:page' AND LOWER(t.title) LIKE '%test%' 

Expected result:

SELECT * FROM [nt:base] AS t WHERE ISDESCENDANTNODE(t, [/]) AND t.[jcr:primaryType] = 'mgnl:page' AND LOWER(t.title) LIKE 'test%' 


 Comments   
Comment by Antonín Juran [ 19/Jan/23 ]

Discovery

If we use LIKE with wildcard as postfix only, we'll limit the searching only to words starting with value from the filter. We could implement configuration of the wildcards.

Generated at Mon Feb 12 09:49:03 CET 2024 using Jira 9.4.2#940002-sha1:46d1a51de284217efdcb32434eab47a99af2938b.