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
>
>
>