You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by Zhengqingzheng <zh...@huawei.com> on 2016/08/04 08:19:16 UTC

Ignite Sql query problem using IN clause

Hi there,
When we using ignite sql query, we find that IN clause cannot use indexing when adding extra 'and xxx' clause.
Here are the descriptions :
If using 'IN' clause like this, and set col1 to be indexed. It works fine.
select * from table1 where col1 in (?,?,?);
However, if you add more 'and' clause like this:
select * from table1 where col1 in (?,?,?) and col2=?;
it will scan the table without going into indexing search.

Any suggestions?

Best regards,
Kevin


Re: Ignite Sql query problem using IN clause

Posted by mike-griggs <en...@gmail.com>.
There is a suggested workaround in
https://apacheignite-sql.readme.io/docs/performance-and-debugging#section-sql-performance-and-usability-considerations,
item 2

 As a workaround...you can rewrite the query in the following way: select
p.name from Person p join table(id bigint = ?) i on p.id = i.id.



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/

Re: Ignite Sql query problem using IN clause

Posted by Naveen <na...@gmail.com>.
Has it been fixed in 2.4 ?

Looks like it is not, when I generate the explain plan for a query which has
a composite key

Here are the queries I ran and explain plan results.
When I use IN, it uses the full table scan, otherwise It does look up on
primary key

explain SELECT * FROM "MapLonglistCache".MAP_LONGLIST  WHERE 
(ENTITY_ID,RELATIONSHIP,SEQUENCE_NO) = ('12345', 'OutageID_ServiceIDs', '9') 

SELECT
    __Z0.ENTITY_ID AS __C0_0,
    __Z0.MAPPING_ID_LIST AS __C0_1,
    __Z0.RELATIONSHIP AS __C0_2,
    __Z0.UPDATEDDATETIME AS __C0_3,
    __Z0.UPDATEDBY AS __C0_4,
    __Z0.SEQUENCE_NO AS __C0_5,
    __Z0.TUPLE_COUNT AS __C0_6
FROM "MapLonglistCache".MAP_LONGLIST __Z0
    /* "MapLonglistCache".MAP_LONGLIST.__SCAN_ */
WHERE (__Z0.ENTITY_ID, __Z0.RELATIONSHIP, __Z0.SEQUENCE_NO) = ('12345',
'OutageID_ServiceIDs', '9')

explain SELECT * FROM "MapLonglistCache".MAP_LONGLIST  WHERE 
ENTITY_ID = '12345' AND 
RELATIONSHIP = 'OutageID_ServiceIDs' AND 
SEQUENCE_NO='9' 


SELECT
    __Z0.ENTITY_ID AS __C0_0,
    __Z0.MAPPING_ID_LIST AS __C0_1,
    __Z0.RELATIONSHIP AS __C0_2,
    __Z0.UPDATEDDATETIME AS __C0_3,
    __Z0.UPDATEDBY AS __C0_4,
    __Z0.SEQUENCE_NO AS __C0_5,
    __Z0.TUPLE_COUNT AS __C0_6
FROM "MapLonglistCache".MAP_LONGLIST __Z0
    /* "MapLonglistCache".MAP_LONGLIST_PK: SEQUENCE_NO = '9'
        AND ENTITY_ID = '12345'
        AND RELATIONSHIP = 'OutageID_ServiceIDs'
     */
WHERE (__Z0.SEQUENCE_NO = '9')
    AND ((__Z0.ENTITY_ID = '12345')
    AND (__Z0.RELATIONSHIP = 'OutageID_ServiceIDs'))

Any suggestions on this.

Thanks
naveen



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/

Re: Ignite Sql query problem using IN clause

Posted by Sergi Vladykin <se...@gmail.com>.
It looks like an issue in H2 database engine, but it was fixed in newest
versions.

In Ignite-1.7.0 (which is on the vote right now) we upgraded H2, so it must
be ok.

Sergi

2016-08-04 11:19 GMT+03:00 Zhengqingzheng <zh...@huawei.com>:

> Hi there,
>
> When we using ignite sql query, we find that IN clause cannot use indexing
> when adding extra ‘and xxx’ clause.
>
> Here are the descriptions :
>
> If using ‘IN’ clause like this, and set col1 to be indexed. It works fine.
>
> select * from table1 where col1 in (?,?,?);
>
> However, if you add more ‘and’ clause like this:
>
> select * from table1 where col1 in (?,?,?) and col2=?;
>
> it will scan the table without going into indexing search.
>
>
>
> Any suggestions?
>
>
>
> Best regards,
>
> Kevin
>
>
>