Hi Set,

Would you mind elaborating on the following paragraph below:

> If you come from a desktop database background, one big change is
> that client/server databases encourage selecting only a few
> records rather than a large number of records. It may not sound
> like a big difference, but until you start doing this Firebird
> will feel like a big, slow mastodont of a database, whereas the
> real problem is more the way you use it (like pigeons are suitable
> for carrying small messages, not the 700 pages Firebird book that
> Helen is writing).

I'm new to Firebird and trying to get my head around it.

Many thanks,

David

PS Do you know when Helen's book is due out?

--- In firebird-support@yahoogroups.com, Svein Erling Tysvaer
<svein.erling.tysvaer@k...> wrote:

> At 00:27 19.07.2003 +0000, you wrote:
> >hi Svein, Iam having problem reading the plan sort. Here is my

very

> >slow query I want to work on it.
> >
> >select m."customerid",
> > m."invdate", d."invid", d."invno", d."invdetailsid",
> >d."counterid",
> > d."item", d."qty", d."m3", d."rate", d."otherqty",
> > d."qty2", d."m32", d."rate2", d."otherqty2",d."priceby",
> >d."priceby2", d."lacking",
> > d."adjustment", d."discount", d."discount2", d."losses",
> >d."damage", d2."etd", d2."port",
> > d."drqty", d."packingid",
> > m."status"
> >from "invoicedetails" d inner join "invoice" m on m."invid" =
> >d."invid"
> > left join "packing" d2 on d."packingid" =
> >d2."packingid";
> >
> >the plan sort is this ...
> >
> >PLAN JOIN (JOIN (M NATURAL,D INDEX (invoicedetails_IDX2)),D2 INDEX
> >(INTEG_4))
> >
> >here are my indexes
> >
> >"invoicedetails": IDX1(packingid, invdetailsid), IDX2(invid), IDX3
> >(counterid)
> >"invoice": primarykey("invid"), IDX1("invdate","customer")
> >"packing": primarykey("packingid"), IDX1("etd","containerno")
> >
> >
> >Iam new in reading the plan sort, what is na meaning of NATURAL?
>
> To me, this actually looks as the best possible plan for your

query.

> NATURAL means that it does not use an index for M, but since you

have no

> WHERE clause limiting M or D, there is no way it could benefit

from using

> an index. Also, since it is the first table in your plan, it does

not slow

> things down too much (if it had used NATURAL for D as well, it

would have

> had to scan the entire table of D for every row of M, and your

query would

> have slowed down a lot). Since you are using LEFT JOIN, D2 cannot

be

> included within the same join bit (note the parenthesis) as M and

D and it

> just uses the index it can afterwards. In short, your plan looks

good.

>
> >I also read on your conversation between you and Edwin. You

suggest

> >that ... for example if I have a Status field or Invoice Date

field

> >and I have index it because what the IB manual says that if your
> >performing a search in a field or you often used that field in

your

> >where clause you have to index it to perform fast searching, then

you

> >say that for example my status field only has 3 possible values

so it

> >is better if I drop that index?
>
> Well, yes, normally. Though in addition to selectivity, you should

also

> consider distribution. E.g. if your status field contained the

values

> "Archived", "OK" and "Error", where 99% of the records had status
> "Archived" and you only ever selected records containing the other

two

> values, then an index on (STATUS, <Primary Key>) would be

appropriate. I

> add the <Primary Key> to avoid a large number of duplicates within

the

> index - something which make updates and deletes slow.
>
> >As you saw I have the IDX1 in invoice and IDX1 in packing which

has

> >two fields, thats because I have some operation where both two

field

> >where always use and the same time. Could my search be optimize

have

> >these index?
>
> Only if those fields (or, to be precise, if at least the first of

those two

> fields) limit the records returned through the WHERE or JOIN

clauses. If

> you come from a desktop database background, one big change is

that

> client/server databases encourage selecting only a few records

rather than

> a large number of records. It may not sound like a big difference,

but

> until you start doing this Firebird will feel like a big, slow

mastodont of

> a database, whereas the real problem is more the way you use it

(like

> pigeons are suitable for carrying small messages, not the 700

pages

> Firebird book that Helen is writing).
>
> Set