Stop thinking tables, start thinking datasets!

Indexes are useful in WHERE and JOIN clauses, selects that return entire tables normally don't benefit much from using an index (it might be a bit quicker or slower, but the difference isn't huge).

Normally, try to use WHERE and/or JOIN clauses so that your query returns a handful of records rather than 30000. Sure, sometimes you need 30000 records, e.g. when data should be exported for import into another system. However, in such cases most people don't mind waiting 10 seconds for the export to finish. I've never seen a case where it has been useful to display a result set of 30000 records.

If you're used to desktop databases (like Access), then you're probably used to thinking in terms of tables rather than datasets. It's like when changing from using a bicycle to a car, for pavements or in the forest, you'll still find the bicycle superior. You're unlikely to appreciate Firebird when you only ask for entire tables.

Sorry if I'm wrong and that when you say 'ages', you're talking about hours rather than seconds. If so, then there is something wrong.

HTH,
Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of kerryneighbour
Sent: 22. juni 2011 11:35
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Slow query (unindexed reads)

I am using FB 2.5 in Windows.

I am trying to optimize some rather slow queries. Some (most?) indexes do not seem to be working.

For example - I have a query that I have simplified to

SELECT ID,ITEMNUMBER,ITEMDESC FROM MYTABLE ORDER BY ITEMNUMBER

I run this on a table of about 30,000 records. It takes ages, and IBExpert tells me that all the reads are UNINDEXED.

There is a primary index on ID, and an index on the sort field

ALTER TABLE LOCATIONITEMS ADD CONSTRAINT PK_LOCATIONITEMS PRIMARY KEY (ID);

CREATE INDEX LOCATIONITEMS_ITEMID ON LOCATIONITEMS (ITEMID);


This happens on many of my queries - I only show this one as it is very simple.

The plan is PLAN SORT (SORT ((LOCATIONITEMS NATURAL)))

Can anyone see why the index is not being used?