Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <771201638.1225914224447.JavaMail.jira@brutus>
Date: Wed, 5 Nov 2008 11:43:44 -0800 (PST)
From: "Martin Hajduch (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Commented: (DERBY-3937) Select count(*) scans all the rows
 (and is therefore slow with big tables), is the amount of rows not
 available/known for example in index ?
In-Reply-To: <484760931.1225904506843.JavaMail.jira@brutus>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


    [ https://issues.apache.org/jira/browse/DERBY-3937?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12645303#action_12645303 ] 

Martin Hajduch commented on DERBY-3937:
---------------------------------------

Ok. I understand your explanation. At the same time, I can imagine that there are applications which need functions like COUNT/MIN/MAX to be performed faster then by scanning through all rows. Also, some applications would need to implement support for 'paging' - for which I haven't found any viable solution yet using Derby.

Are there any plans of development in these directions ? Does Derby has something 'else' to make it possible to develop 'paging' over huge amount of rows ? Or is this not priority at the moment ?

Are there some speedups possible ? For example count of index entries per page ? Or would that not bring any significant benefit over current scan ?

Any idea how Oracle manages this ?

> Select count(*) scans all the rows (and is therefore slow with big tables), is the amount of rows not available/known for example in index ?
> --------------------------------------------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-3937
>                 URL: https://issues.apache.org/jira/browse/DERBY-3937
>             Project: Derby
>          Issue Type: Improvement
>          Components: Performance
>         Environment: Any
>            Reporter: Martin Hajduch
>
> Create table with 5000000 rows. Create index on unique ID. Select count(*) on such table is going to take quite some time.
> Shouldn't the index contain amount of indexed rows and the value taken from there ?
> Additionally, queries of the form select count(*) from table where col1=value; take lots of time (depending on amount of rows satisfying WHERE clause) even if index on col1 exists. Isn't it possible to find first and last occurence in the index, and then calculate amount of rows more effectively then scanning through all of them ?

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.