Subject Re: [firebird-support] How to index?
Author Daniel L. Miller
Ales Smodis wrote:

>Hello everyone,
>I have a table with 16.2 million records:
>create table item
> site SmallInt not null,
> order Integer not null,
> sn SmallInt not null,
> ean Decimal(13),
> qty Decimal(9, 3),
> price Decimal(14, 2),
> constraint item_pk primary key (site, order, sn)
>and I want to run the following query:
>select ean, site, sum(qty), sum(price)
>from item
>group by ean, site;
>This takes about 4.5 minutes, the plan being: plan sort ((item natural))
>Then I want to be clever about it and do this:
>create index item_ean_site on item (ean, site);
>and rerun the query, now the plan being: plan (item order
>item_ean_site). Now it takes a little over 14 minutes. Shouldn't now the
>query actually run a bit faster, if it doesn't have any additional
>sorting to do?
>Should I create a different index? Should I use an index at all?
>I'm using Firebird 1.5.2.rc1.
> -Ales
If your table is that big, and your query operation speed is
unacceptable, you might want to consider splitting it.