Ignite has full text search capabilities (based on Lucene engine) with TextQuery [1] that should perform better for partial matches. Example: [2]
[1] https://ignite.apache.org/releases/latest/javadoc/org/apache/ignite/cache/query/TextQuery.html [2] https://github.com/apache/ignite/blob/master/examples/src/main/java/org/apache/ignite/examples/datagrid/CacheQueryExample.java#L147 On Fri, Jan 6, 2023 at 9:22 AM Biraj Deb < [email protected]> wrote: > Hi all, > What is the best way to search partial text and ignore cases from a table > to get best performance? > Our backend code in *Java. *Our environment has RAM: 61 gib, cpu: 16 > vcpus, core : 8. > I have tried to index on that field and I'm still getting results in 4 > sec. > Query Is : > *SELECT OBJ.PROP_VALUE_STR1 as name FROM DFM.BRANCH B INNER JOIN > DFM.OBJECT OBJ ON 1=1 WHERE (OBJ.BRANCH_ID = B.ID <http://B.ID>) AND > ((CAST(OBJ.PROP_VALUE_STR1 AS VARCHAR_IGNORECASE) LIKE 'qualify%') AND > ((OBJ.ID <http://OBJ.ID> <> '6a7a51ce-3fc9-4b35-a845-2be95c8180a1') AND > ((OBJ.REFERENCE_ID <> '6a7a51ce-3fc9-4b35-a845-2be95c8180a1') AND > ((B.PROJECT_ID = 'cad78e78-a38b-4340-817b-d689b988ec17') AND > (OBJ.OBJECT_TYPE_ID =1)))))* > In above Query join two table in that *OBJECT* table has index on > BRANCH_ID and PROP_VALUE_STR1 and *BRANCH* table has index on > PROJECT_ID. And Object table has more than a million entry > > > > >
