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