Hi, Kevin. Have you defined other composite indexes including objid, fieldnum fields ? If yes, try to disable them and check if query picks up correct index.
2016-07-05 15:45 GMT+03:00 Zhengqingzheng <zhengqingzh...@huawei.com>: > Hi Alexei, > > Indexing not work again. This time I print the query plan: > > > > [SELECT > > GUID AS __C0 > > FROM "com.huawei.soa.ignite.model.IgniteMetaIndex_Cache".IGNITEMETAINDEX > > /* > "com.huawei.soa.ignite.model.IgniteMetaIndex_Cache"."objId_fieldNum_numValue": > OBJID = ?1 > > AND FIELDNUM = ?2 > > */ > > WHERE (STRINGVALUE = ?3) > > AND ((OBJID = ?1) > > AND (FIELDNUM = ?2))], [SELECT > > __C0 AS GUID > > FROM PUBLIC.__T0 > > /* "com.huawei.soa.ignite.model.IgniteMetaIndex_Cache"."merge_scan" */] > > > > It seems that the group index is broken, as I can see only objId and > fieldnum are used to check the index, however, I defined three columns: > objid, fieldnum and stringvalue > > And the order is incorrect. > > My query is : > > String qryStr = " explain select guid from IgniteMetaIndex where objid=? > and fieldnum=? and stringvalue=? "; > > … > > fieldQry.setArgs( "a1162", 7 ,"18835473249"); > > > > hope you can help me. > > > > > > Best regards, > > Kevin > > *发件人:* Alexei Scherbakov [mailto:alexey.scherbak...@gmail.com] > *发送时间:* 2016年6月29日 0:30 > *收件人:* user > *主题:* Re: ignite group indexing not work problem > > > > Hi, > > > > I've tried the provided sample and found what instead of using > oId_fNum_num index H2 engine prefers oId_fNum_date, > > thus preventing condition on num field to use index. > > > > I think it's incorrect behavior. > > > > Could you disable oId_fNum_date, execute the query again and provide me > with the query plan and execution time ? > > > > You can get query plan from build-in H2 console. Read more about how to > setup console here [1] > > > > [1] https://apacheignite.readme.io/docs/sql-queries#using-h2-debug-console > > > > > > > > > > 2016-06-28 6:08 GMT+03:00 Zhengqingzheng <zhengqingzh...@huawei.com>: > > Hi there, > > My ignite in-memory sql query is very slow. Anyone can help me to figure > out what was wrong? > > > > I am using group indexing to speed up in-memory sql queries. I notice that > my sql query took 2274ms (data set size: 10Million, return result:1). > > > > My query is executed as: > > String qryStr = "select * from UniqueField where oid= ? and fnum= ? and > num= ?"; > > > > String oId="a343"; > > int fNum = 3; > > BigDecimal num = new BigDecimal("510020000982136"); > > > > IgniteCache cache = > igniteMetaUtils.getIgniteCache(IgniteMetaCacheType.UNIQUE_INDEX); // to > get selected cache ,which has been created in some other place > > > > SqlQuery qry = new SqlQuery(UniqueField.class, qryStr); > > qry.setArgs(objId,fieldNum, numVal); > > long start = System.currentTimeMillis(); > > List result= cache.query(qry).getAll(); > > long end = System.currentTimeMillis(); > > System.out.println("Time used in query :"+ (end-start)+"ms"); > > > > And the result shows: Time used in query :2274ms > > > > I have set group indexes, and the model is defined as: > > import java.io.Serializable; > > import java.math.BigDecimal; > > import java.util.Date; > > > > import org.apache.ignite.cache.query.annotations.QuerySqlField; > > > > public class UniqueField implements Serializable > > { > > > > @QuerySqlField > > private String orgId; > > > > @QuerySqlField( > > orderedGroups={ > > @QuerySqlField.Group( > > name="oId_fNum_ msg ", order=1, descending = true), > > @QuerySqlField.Group( > > name="oId_fNum_ num ", order=1, descending = > true), > > @QuerySqlField.Group( > > name="oId_fNum_ date ", order=1, descending = true) > > > > }) > > private String oId; > > > > @QuerySqlField(index=true) > > private String gId; > > > > @QuerySqlField( > > orderedGroups={ > > @QuerySqlField.Group( > > name="oId_fNum_ msg ", order=2, descending = true), > > @QuerySqlField.Group( > > name="oId_fNum_ num ", order=2, descending = > true), > > @QuerySqlField.Group( > > name="oId_fNum_ date ", order=2, descending = true) > > > > }) > > private int fNum; > > > > @QuerySqlField(index=true, @QuerySqlField.Group( > > name="oId_fNum_ msg ", order=3, descending = true)}) > > private String msg; > > > > @QuerySqlField(index=true, @QuerySqlField.Group( > > name="oId_fNum_ num ", order=3, descending = true)}) > > private BigDecimal num; > > > > @QuerySqlField(index=true, @QuerySqlField.Group( > > name="oId_fNum_ date ", order=3, descending = true)}) > > private Date date; > > > > public UniqueField(){}; > > > > public UniqueField( > > String orgId, > > String oId, > > String gId, > > int fNum, > > String msg, > > BigDecimal num, > > Date date > > ){ > > this.orgId=orgId; > > this.oId=oId; > > this.gId = gId; > > this.fNum = fNum; > > this.msg = msg; > > this.num = num; > > this.date = date; > > } > > > > public String getOrgId() > > { > > return orgId; > > } > > > > public void setOrgId(String orgId) > > { > > this.orgId = orgId; > > } > > > > public String getOId() > > { > > return oId; > > } > > > > public void setOId(String oId) > > { > > this.oId = oId; > > } > > > > public String getGid() > > { > > return gId; > > } > > > > public void setGuid(String gId) > > { > > this.gId = gId; > > } > > > > public int getFNum() > > { > > return fNum; > > } > > > > public void setFNum(int fNum) > > { > > this.fNum = fNum; > > } > > > > public String getMsg() > > { > > return msg; > > } > > > > public void setMsg(String msg) > > { > > this.msg = msg; > > } > > > > public BigDecimal getNum() > > { > > return num; > > } > > > > public void setNum(BigDecimal num) > > { > > this.num = num; > > } > > > > public Date getDate() > > { > > return date; > > } > > > > public void setDate(Date date) > > { > > this.date = date; > > } > > > > } > > > > > > > > > > > > > > -- > > > Best regards, > > Alexei Scherbakov > -- Best regards, Alexei Scherbakov