Hi,

Using DBISAM 4.24 and ODBC driver.

I have two tables box.dat and boxline.dat with a master-detail relation.

I have the following query:
select * from box b left outer join boxline l on l.[Box ID]=b.[Box ID] where
([User ID]=123) and ([Tab ID]=456) order by
[Column],[Minimized],[Row],[Sequence Number]

What is the best way to index the table box. Does a query first run on the
WHERE statement and then on the ORDER BY statement. So then the order should
be [User ID],[Tab ID],[Column],[Minimized],[Row].  The field [Sequence
Number] is a field in table boxline.


I assume that boxline should be indexed on [Box ID],[Sequence Number]

Thanks for your advice.

Andre

Andre


The best suggestion is that all fields involved in a join or where clause should have single field indices, and that ideally there should be a compound index representing the order by clause.

Roy Lambert

Andre,

<< Does a query first run on the WHERE statement and then on the ORDER BY
statement. >>

Yes.  Make sure that there is at least an index consisting of the columns
used in the ORDER BY, and then DBISAM can return a live result set, which is
very quick.  Ideally, you should also make sure that there is an index on at
least one of the fields used in the WHERE clause, ideally the most selective
field:

http://www.elevatesoft.com/dbisam4d5_optimizations.htm

--
Tim Young
Elevate Software
www.elevatesoft.com

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:3E7C9865-A7E5-4CBC-B654-E7234038E0FE@news.elevatesoft.com...
> Andre,
>
> << Does a query first run on the WHERE statement and then on the ORDER BY
> statement. >>
>
> Yes.  Make sure that there is at least an index consisting of the columns
> used in the ORDER BY, and then DBISAM can return a live result set,

OP has a JOIN in the query, I doubt it will return a live set.

Robert

Robert,

<< OP has a JOIN in the query, I doubt it will return a live set. >>

Yep, missed that.

--
Tim Young
Elevate Software
www.elevatesoft.com