Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Date: Mon, 5 Jun 2017 13:13:04 +0000 (UTC)
From: "Jim Cross (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Message-ID: <JIRA.13077241.1496668383000.364922.1496668384024@Atlassian.JIRA>
In-Reply-To: <JIRA.13077241.1496668383000@Atlassian.JIRA>
References: <JIRA.13077241.1496668383000@Atlassian.JIRA> <JIRA.13077241.1496668383084@jira-lw-us.apache.org>
Subject: [jira] [Created] (DERBY-6939) Update statement poor performance
 with index using where in (select
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit
archived-at: Mon, 05 Jun 2017 13:13:07 -0000

Jim Cross created DERBY-6939:
--------------------------------

             Summary: Update statement poor performance with index using where in (select
                 Key: DERBY-6939
                 URL: https://issues.apache.org/jira/browse/DERBY-6939
             Project: Derby
          Issue Type: Bug
          Components: SQL
    Affects Versions: 10.11.1.1
            Reporter: Jim Cross


An update statement such as below doesn't appear to be using the index:

update mytable set col1 = 'myvalue' where indexed_col in (select index_col from mytable where col1 like 'myv%' fetch first 100 rows only);

The table in this case has over a million rows. Running the sub-query:

select index_col from mytable where col1 like 'myv%' fetch first 100 rows only

Returns almost immediately with 100 rows but this update takes over two minutes. Update does not seem to be using index because originally we try the query without the index and performance after adding the index seemed to be the same.


--
This message was sent by Atlassian JIRA
(v6.3.15#6346)