Hi,

> 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))

Natural: reading all rows.

Which makes sense, as you do not have a
WHERE clause.

> 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?

Reading the index AND reading the data -> this
takes more time.

In general (Arno will disagree with me here), I think that
indices should be used for quick lookups (WHERE <condition>)
and sorting should be done only via an index if it makes sense.

eg: return the first couple of rows of a resultset ordered by
an indexed column.

> Should I create a different index? Should I use an index at all?

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com