Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: neutral (nike.apache.org: 32.97.110.152 is neither permitted nor
 denied by domain of qozinx@gmail.com)
Message-ID: <47B0DEFE.6060905@gmail.com>
Date: Mon, 11 Feb 2008 15:49:18 -0800
From: Army <qozinx@gmail.com>
User-Agent: Thunderbird 2.0.0.6 (Windows/20070728)
MIME-Version: 1.0
To: Derby Discussion <derby-user@db.apache.org>
Subject: Re: COUNT() optimisation
References: <200802080925.35160.daniel@nuix.com> <47ACADBD.3030603@gmail.com>
 <200802111010.35312.daniel@nuix.com>
In-Reply-To: <200802111010.35312.daniel@nuix.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Daniel Noll wrote:
> 
> I created bogus test data where jobid was always 0 and state was
> cycled between 0,1,2.
> 
> Query:
> SELECT COUNT(*) FROM jobitems WHERE jobid = 0 AND state = 0

Given this particular query pattern I would say that increased times for 
larger tables makes sense.  Since jobid is always 0 and state has one of 
three values, if you increase the number of rows from 100 to 1 million 
it stands to reason that there are going to be a lot more rows matching 
the WHERE criteria, and so it will take longer to count those rows.

For the record, when I changed the data and the WHERE clause so that 
COUNT(*) only returns a single row, the times I saw were constant 
regardless of how many rows were in the table--which is what I would 
expect since we're using the index.

> If I log the query plan it confirms what's happening.  It estimates
> the number of rows which will come back and then says it's iterating
> through each row to determine the count.

Okay, I think that matches what I mentioned above...

> Problem is that two users adding or removing items at the same time
> would be updating the same table and I'm not sure how clever Derby is
> if two users do an UPDATE at the same time which adds or subtracts
> from the existing value.

Unless I'm missing the heart of your question, I think this is a fairly 
normal transactional task for Derby: two users trying to update the same 
row at the same time.  Derby should use transaction locking to ensure 
that both users' updates are correctly reflected in the table.

> If it merged them together as expected then  I'm guessing would be
> a viable solution.

I agree, it seems like that should work (assuming "merge" here means 
"correctly apply both users' changes to the table"). If you try it out 
and run into problems, though, you can always post back with a follow-up 
mail...

Army