Mailing-List: contact issues-help@ignite.apache.org; run by ezmlm
Precedence: bulk
Reply-To: dev@ignite.apache.org
Date: Tue, 3 Jan 2017 07:07:58 +0000 (UTC)
From: "Vladimir Ozerov (JIRA)" <jira@apache.org>
To: issues@ignite.apache.org
Message-ID: <JIRA.13016151.1477660653000.626500.1483427278414@Atlassian.JIRA>
In-Reply-To: <JIRA.13016151.1477660653000@Atlassian.JIRA>
References: <JIRA.13016151.1477660653000@Atlassian.JIRA> <JIRA.13016151.1477660653296@arcas>
Subject: [jira] [Updated] (IGNITE-4150) B-Tree index cannot be used
 efficiently with IN clause.
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit
archived-at: Tue, 03 Jan 2017 07:08:00 -0000


     [ https://issues.apache.org/jira/browse/IGNITE-4150?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Vladimir Ozerov updated IGNITE-4150:
------------------------------------
    Description: 
Consider the following query:
{code}
SELECT * FROM table
WHERE a = ? AND b IN (?, ?)
{code}

If there is an index {{(a, b)}}, it will not be used properly: only column {{a}} will be used. This will leads to multiple unnecessary comparisons.

Most obvious way to fix that - use temporary table and {{JOIN}}. However, this approach doesn't work well when there are multiple {{IN}}'s. 

Proper solution would be to hack deeper into H2.

  was:
Consider the following query:
{code}
SELECT * FROM table
WHERE a = ? AND b IN (?, ?)
{code}

If there is an index {{(a, b)}}, it will not be used properly: only column {{a}} will be used. This will leads to multiple unnecessary comparisons.

Most obvious way to fix that - use temporary table and {{JOIN}}. However, this approach doesn't work well when there are multiple {{IN}}s. 

Proper solution would be to hack deeper into H2.


> B-Tree index cannot be used efficiently with IN clause.
> -------------------------------------------------------
>
>                 Key: IGNITE-4150
>                 URL: https://issues.apache.org/jira/browse/IGNITE-4150
>             Project: Ignite
>          Issue Type: Task
>          Components: SQL
>    Affects Versions: 1.7
>            Reporter: Vladimir Ozerov
>              Labels: performance
>             Fix For: 2.0
>
>
> Consider the following query:
> {code}
> SELECT * FROM table
> WHERE a = ? AND b IN (?, ?)
> {code}
> If there is an index {{(a, b)}}, it will not be used properly: only column {{a}} will be used. This will leads to multiple unnecessary comparisons.
> Most obvious way to fix that - use temporary table and {{JOIN}}. However, this approach doesn't work well when there are multiple {{IN}}'s. 
> Proper solution would be to hack deeper into H2.


--
This message was sent by Atlassian JIRA
(v6.3.4#6332)