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: pass (athena.apache.org: local policy)
MIME-version: 1.0
Content-transfer-encoding: 7BIT
Content-type: text/plain; CHARSET=US-ASCII
Date: Sun, 13 Jun 2010 13:24:36 +0200
From: Knut Anders Hatlen <Knut.Hatlen@Sun.COM>
Subject: Re: When is multi field index preferable over single field ?
In-reply-to: <AANLkTilWcIE-jTZEEwuLyjLkuERlHwn5W2nnj4Dm8XNK@mail.gmail.com>
Sender: Knut.Hatlen@Sun.COM
To: derby-user@db.apache.org
Message-id: <4C14BFF4.6000802@sun.com>
References: <AANLkTilWcIE-jTZEEwuLyjLkuERlHwn5W2nnj4Dm8XNK@mail.gmail.com>
User-Agent: Mozilla/5.0 (X11; U; SunOS i86pc; en-US;
 rv:1.9.1.9) Gecko/20100524
 Lightning/1.0b1 Thunderbird/3.0.4

On 06/12/10 07:58 AM, Damian Carey wrote:
> Hello,
>
> We have a simple database, only 3 tables with a few fields in each)
> that contains data supplied by a third party. Almost entirely read
> only.
>
> The table of concern has about 380K rows, and on each retrieval we
> will retrieve between 10 and 500 rows.
>
> I am looking for some BASIC advice on which indexes should be created
> to assist with standard queries.
>
> I am just looking for reasonable advice, and am not worried about any
> super optimal solution.
>
> The two basic queries are as follows ...
>
> SELECT * FROM MYTABLE qt WHERE qt.scheduleId = 'ABC' AND qt.body = '4DR'
>
> and
>
> SELECT DISTINCT qt.body FROM MYTABLE qt WHERE qt.scheduleId = 'ABC'
>
> Are "multi field" indexes preferable or would single field indexes work OK.
>
> Perhaps something like this for a multi field ....
>
> CREATE INDEX T_SCHED_BODY_IDX ON MYTABLE (scheduleId, body)
>
> Or perhaps something like this for a single field ....
> CREATE INDEX T_SCHED_IDX ON MYTABLE (scheduleId)
> CREATE INDEX T_BODY_IDX ON MYTABLE (body)
>
> Any guidance, advice, clues, suggestions, experience would be most appreciated.
>   

Hi Damian,

I think the multi-field index would be preferable in both of these cases.

1) SELECT * FROM MYTABLE qt WHERE qt.scheduleId = 'ABC' AND qt.body = '4DR'


Here, if we only have the two single-field indexes, the query will only
use one of them. That means (if the first index is picked) that the
query will have to look at all the rows that have scheduleId='ABC' and
check if they also have body='4DR'. If many of those rows don't have
body='4DR', we have wasted time looking at rows that shouldn't be
included in the result.

With the multi-field index, we'll only have to look at the rows that
have both scheduleId='ABC' and body='4DR', so we don't waste time
looking at rows that don't qualify.

2) SELECT DISTINCT qt.body FROM MYTABLE qt WHERE qt.scheduleId = 'ABC'


In this query, a single-field index on scheduleId and a multi-field
index with scheduleId as the first field will end up looking at the same
number of rows. However, the multi-field index gives these additional
benefits:

    * Since the index also contains the field body, all the data needed
      to produce the result can be found in the index. This saves one
      lookup in the base table per returned row.
    * The multi-field index will return the rows ordered on body, so we
      don't need to sort the result or build a hash table in order to
      eliminate duplicates as required by the DISTINCT keyword.


Hope this helps,

-- 
Knut Anders