Re: Query execution too long even after providing index

[Prasad Bhalerao <pr...@gmail.com>]

Hi, Evgenii,

I tried to execute sql without UNION ALL operator. I mean I just executed
the first part of the sql as shown below and it is taking 700-800 ms to
complete. I have around 3 million records in my cache. and will be having
around 30-40 million records in real scenario.

SELECT id, moduleId,ipEnd, ipStart
FROM IpContainerIpV4Data USE INDEX(ip_container_ipv4_idx1)
WHERE subscriptionId = ?  AND moduleId         = ? AND (ipStart        <= ?
AND ipEnd           >= ?)

Does ignite executes 3 sqls independently and then does the union
operation? Is this the reason it is taking time?

Is there any solution to solve this problem? The sql shown below is the
very basic sql which is needed everywhere in my application. Since ignite
does not use index with OR clause I had to rewrite the same sql with UNION
ALL operator.
As mentioned in this email thread, this sql is using indexes. Now the
question is does ignite brings all filtered data in heap space and then
does union all operation?


SELECT id, moduleId,ipEnd, ipStart
 FROM IpContainerIpV4Data USE INDEX(ip_container_ipv4_idx1)
 WHERE subscriptionId = ?  AND moduleId         = ? AND (ipStart        <=
? AND ipEnd           >= ?)
UNION ALL
SELECT id, moduleId,ipEnd, ipStart
 FROM IpContainerIpV4Data USE INDEX(ip_container_ipv4_idx1)
 WHERE subscriptionId = ? AND moduleId         = ? AND (ipStart        <= ?
AND ipEnd           >= ?)
UNION ALL
SELECT id, moduleId,ipEnd, ipStart
 FROM IpContainerIpV4Data USE INDEX(ip_container_ipv4_idx1)
 WHERE subscriptionId = ? AND moduleId         = ? AND (ipStart        >= ?
AND ipEnd           <= ?)



On Thu, Sep 13, 2018 at 9:44 PM ezhuravlev <e....@gmail.com> wrote:

> Hi,
>
> What is the execution time of just a single query without UNION?
>
> Evgenii
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>